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?

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl64, width: 64"]New[/TD]
[TD="class: xl64, width: 64"]Old[/TD]
[TD="class: xl64, width: 64"]Fav(Unfav)[/TD]
[TD="class: xl65, width: 64"]%[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="class: xl63, align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]-100[/TD]
[TD="class: xl63, align: right"]-100%[/TD]
[/TR]
[TR]
[TD="align: right"]-100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-100[/TD]
[TD="class: xl63, align: right"]-100%[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]-100[/TD]
[TD="align: right"]100[/TD]
[TD="class: xl63, align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]80[/TD]
[TD="class: xl63, align: right"]400%[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]-80[/TD]
[TD="class: xl63, align: right"]-80%[/TD]
[/TR]
[TR]
[TD="align: right"]-100[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]-120[/TD]
[TD="class: xl63, align: right"]-600%[/TD]
[/TR]
[TR]
[TD="align: right"]-20[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]-120[/TD]
[TD="class: xl63, align: right"]-120%[/TD]
[/TR]
[TR]
[TD="align: right"]-100[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"]-80[/TD]
[TD="class: xl63, align: right"]-400%[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"]120[/TD]
[TD="class: xl63, align: right"]600%[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.