Common formula for different Cases

[RUPESHRH]

Dear Experts

There are four difference examples but answer should be same If the value of C4 is high then C4/C5 will be the answer and
if the value of C5 is higher then C5/C4 will be the answer.
I need a common formula for both the cases.
Experts Please help !
Thanks in advance

[alansidman]

try this

=IFERROR(IF(C4>C5,(C4/C5)-1,(C5/C4)-1),1)

and copy as appropriate and paste

[Pete_UK]

Another way:

=IFERROR(MAX(C4:C5)/MIN(C4:C5)-1,1)

Hope this helps.

Pete

[josephteh]

If you are calculating variance % for say This Year (TY) and Last Year (LY), then the base should always be Last Year (LY). So, if 1st row is TY figure and 2nd row LY figure, then the variance % should be (TY-LY)/LY or TY/LY-1.

Use this formula: =IF(C5=0,100%*SIGN(C4),(C4-C5)/ABS(C5)).

[RUPESHRH]

Dear Alansidnan
Thanks for the Help

[RUPESHRH]

Dear Pete

Thanks for the Help

[josephteh]

Dear Josep its not working for second case Pete and alan solve it Thanks for the Reply

Thanks for the Rep. It would be good if you can share with us what you are comparing.

[alansidman]

Thanks for the Rep