Percentages

[Guest]

Hi,

There is probably a very simple solution to this but I'll ask anyway!

I'd like a simple percentage formula that takes into account all
eventualities ie zero amounts, negative and positive amounts and doesn't
return a DIV0#.

The data looks something like this
A1 - 0
B1 - (255)

A2 - (255)
B2 - (111)

A3 - 155
B3 - 75

etc

I've started something that has become a string of IF and ANDs, there must
be something easier!.

Any help gratefully appreciated.

 

[Roger Govier]

Hi David

Does this achieve what you are looking for
=IF(A1=0,0,(B1-A1)/A1)*SIGN(A1)

 

[Guest]

If cell B1 contains the divisor I would simply do the following if

=if(B1 = 0,100,"enter your normal formula here")

If the divisor is 0, then the percenttage is very close to 100%.

 

[Bernard Liengme]

Roger was short changed for coffee this morning and dropped a few
parentheses

=IF(A1=0,0,((B1-A1)/A1)*SIGN(A1))

best wishes

 

[Roger Govier]

Hi Bernard

Definitely right about the shortage of caffeine this morning.
However, I don't think the extra parentheses makes any difference.
I had used "" as the return to 0 value in A1, and that gave me an error
because of multiplying by SIGN, but changing it to 0 resolved the
problem.

Your extra parentheses do allow the use of Null (if required) rather
than 0 as in
=IF(A1=0,"",((B1-A1))*SIGN(A1))

 

[Roger Govier]

=IF(A1=0,"",((B1-A1))*SIGN(A1))

should of course be
=IF(A1=0,"",((B1-A1)/A1)*SIGN(A1))

(hit delete accidentally when the spellchecker suggested repetition of
A1)
Now where's that coffee percolator?<bg>

 

[Guest]

Thanks for you help R B and J. The problem is solved.

Enjoy your coffee!!!

David