help with formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ted - 21 Nov 2005 02:16 GMT

Hi, can anyone tell me how to get:

=ROUND(D23*C$42,3)

answer appears in cell E23

to run when some cells are blank please? I have other formula doing similar
stuff, but are unsure if the principles are generic - cant get it to work
either way, so are open to suggestions??

Ted.

Reply to this Message

Mike G - 21 Nov 2005 02:39 GMT

whats in D23 and C42?

> Hi, can anyone tell me how to get:
>
[quoted text clipped - 8 lines]
>
> Ted.

Reply to this Message

Ted - 21 Nov 2005 02:50 GMT

numbers - just using the "general" function

> whats in D23 and C42?
>
[quoted text clipped - 10 lines]
> >
> > Ted.

Reply to this Message

Jerry W. Lewis - 21 Nov 2005 03:47 GMT

I think Mike was trying to get you to say more about what you are
wanting and why you are not happy with what you are getting.

Your formula should "run" when cells are blank, but in that case will
return 0. If it is returning someting else, then the cells are likely
not really blank. If cells D23 and C42 contain formulas, then you need
to disclose what those formulas are.

Jerry

> numbers - just using the "general" function
>
[quoted text clipped - 12 lines]
>>>
>>>Ted.

Reply to this Message

Ted - 21 Nov 2005 04:26 GMT

I was trying to get it to ignore empty cells and leave the cell where the sum
would normally display blank (as in without a zero)

I've used this now:

=IF(N(D24)=0,"",ROUND(D24*C48,3))

thanks for your help though, Ted.

> I think Mike was trying to get you to say more about what you are
> wanting and why you are not happy with what you are getting.
[quoted text clipped - 22 lines]
> >>>
> >>>Ted.

Reply to this Message

Jerry W. Lewis - 21 Nov 2005 13:41 GMT

An IF formula is the standard way to do it. Your condition could be
simplified from N(D24)=0 to D24=0 unless the issue is that it might
contain text. If it is possible for D24 to contain an error that you
want to ignore, or it you also need to check C48, you might want to consider
COUNT(D24,C48)<2
as your condition.

If all zero values are to be ignored, another possibility would be
Tools|Options|View and uncheck "Zero values"

Jerry

> I was trying to get it to ignore empty cells and leave the cell where the sum
> would normally display blank (as in without a zero)
[quoted text clipped - 4 lines]
>
> thanks for your help though, Ted.

Reply to this Message

Ted - 21 Nov 2005 03:16 GMT

any suggestions Mike?

> whats in D23 and C42?
>
[quoted text clipped - 10 lines]
> >
> > Ted.

Reply to this Message

Mike G - 21 Nov 2005 15:22 GMT

Well Jerry was right, my question was probing for what the expected inputs
are and what the expected results are. i.e. your example for num_digits = 3
meaning you want the answer rounded to 3 places. So if d23=2 and c42=10
then e23=20. Did you want 3 digit results allways?, if so format e23 for
number with 3 digit rounding and then the result = 20.000. If any one of
the multipliers causes the result to be > 3 decimal places then the original
formula (,3) would round to three places. If you do not want a "0" showing
in case d23 or c42 has a "0" value, can you live with TOOLS/OPTIONS and
unchecking "zero values". There are many ways to do the task, but the hard
part is guessing what the expected results are with the given inputs. So,
if you can do that, perhaps we can give you a solution. Mike

> any suggestions Mike?
>
[quoted text clipped - 13 lines]
>> >
>> > Ted.

Reply to this Message

Ted - 21 Nov 2005 17:15 GMT

Hi Mike, I need zeros to be recognised in other formula, so are restricted
with the 'zero values' function.

I have added this formula and it seems to be doing the job:

=IF(N(D24)=0,"",ROUND(D24*C$48,3))

If you notice any issues with the above, then let me know please?

Thanks,
Ted.

> Well Jerry was right, my question was probing for what the expected inputs
> are and what the expected results are. i.e. your example for num_digits = 3
[quoted text clipped - 25 lines]
> >> >
> >> > Ted.

Reply to this Message