ZER0ING 0UT the Zer0

[EggCell]

_Can_anyone_help_save_this_working_man's_wallet?_

I have two sets of monthly expense figures, budgeted (A1) and actua
(B1). In C1 I have =IF(A1=0,0,B1/A1) to calculate how much in % th
actual is over or under the budget without having a #DIV/0! if th
budgeted is zero.

But if both A1 and B1 =0, C1 returns 0% instead of the correct 100% (o
budget). If A1=0 and B1=n (e.g. 50), C1 would still be 0%. The infinit
nature of zero makes analyzing the budget not meaningful.

Even if we convert A1 to very close to zero (eg. .0001), C1 would b
nonsensically high. We need to put a limiter in the function, o
sacrifice that zero somehow to reach a balance between budget accurac
and the bigger picture. I'd hate to take us back to math theory, bu
are there any ideas for a better C1 formula

 

[Gary Thomson]

Hello,

Change your formula to: if(and(a1=0,b1=0),1,if
(a1=0,0,b1/a1))

Happy to Help,

Gary Thomson

 

[jaf]

Tools>options> check "precision as displayed"

--


John

johnf202 at hotmail dot com


: _Can_anyone_help_save_this_working_man's_wallet?_
:
: I have two sets of monthly expense figures, budgeted (A1) and actual
: (B1). In C1 I have =IF(A1=0,0,B1/A1) to calculate how much in % the
: actual is over or under the budget without having a #DIV/0! if the
: budgeted is zero.
:
: But if both A1 and B1 =0, C1 returns 0% instead of the correct 100% (on
: budget). If A1=0 and B1=n (e.g. 50), C1 would still be 0%. The infinite
: nature of zero makes analyzing the budget not meaningful.
:
: Even if we convert A1 to very close to zero (eg. .0001), C1 would be
: nonsensically high. We need to put a limiter in the function, or
: sacrifice that zero somehow to reach a balance between budget accuracy
: and the bigger picture. I'd hate to take us back to math theory, but
: are there any ideas for a better C1 formula?
:
:
: ---
:
:

 

[EggCell]

Thx Gary, that's a creative approach indeed, but it doesn't solve th
larger problem. If A1=0 your formula still returns 0% for C1, whic
doesn't help us reflect on just how much over budget B1 is. Tricky
isn't it?

Even if we tackled that wit
=IF(AND(A1=0,B1=0),0,IF(AND(A1=0,B1>0),B1,B1/A1-1)) to cover all th
bases with our zer0, it assumes the difference between A1 and B1 is no
that high. I live in a place where the currency is not all that strong
, so if A1=0 and B1=50000, you'd end up with a blindingly high resul
in C1 that is too accurate to make any fast sense. If we take the eas
way out and just have C1 values over a certain threshold retur
something like "OMG!", it wouldn't consider the weight of C1 if w
total a whole set of C column data. So maybe an exponential or tiere
percent limiter is the right approach, or otherwise? Is any Neo ou
there who can become 1 with the 0