A better formula?

[Will85]

There has to be a better, more concise formula than this.

I have revenue, and I want to compute favorability between a new period and an old period.

Higher revenue in the new period than the old period is good, less is bad. The % increase or decrease should follow suit.

If the old period had zero revenue and the new period had revenue, the % increase, while not mathematical, would be positive 100%

My data starts in A2. My Fav(Unfav) calc in C2 is A2-B2 (New-Old), my % formula in D2 is as follows: =+IF(OR(A2=0, B2=0), SIGN(C2), C2/ABS(B2))

Is there a better way to compute this?

New	Old	Fav(Unfav)	%
100	0	100	100%
0	100	-100	-100%
-100	0	-100	-100%
0	-100	100	100%
100	20	80	400%
20	100	-80	-80%
-100	20	-120	-600%
-20	100	-120	-120%
-100	-20	-80	-400%
100	-20	120	600%

<tbody>
</tbody>

 

[Scott Huish]

You could shorten it a little bit by writing it like this:
=IF(A2*B2,C2/ABS(B2),SIGN(C2))

 

[Gerald Higgins]

If that formula gives you the results you want, then why exactly do you want something better ?

You can simplify it slightly - this appears to give the same results
=IF(B2=0,SIGN(C2),C2/ABS(B2))

 

[Will85]

thank you. thats an interesting use of the if function.

A2*B2 doesnt really create a question that can be answered by true or false, it just is what it is.

So is the rule behind a question in IF, that anything not zero is True, and anything zero is False?

You could shorten it a little bit by writing it like this:
=IF(A2*B2,C2/ABS(B2),SIGN(C2))

 

[Scott Huish]

Yes, if a number is in that spot, non-zero is TRUE and zero is FALSE.

 

[Gerald Higgins]

In principle, you can do this -

=IF(A1,1,0)

If A1 contains a number, this formula returns 1.

 

[Will85]

Thank you. i would always use ifnumber or ifblank, etc etc

In principle, you can do this -

=IF(A1,1,0)

If A1 contains a number, this formula returns 1.

 

[Scott Huish]

In principle, you can do this -

=IF(A1,1,0)

If A1 contains a number, this formula returns 1.

More specifically, if it contains a non-zero number, it would return 1. If it contained 0, it would return 0.