Double Negative % variances

[molsviking]

An interesting conundrum:

C9 has a figure which could be positive or negative.
H9 also has a figure which could be positive or negative.

If both figures in C9 & H9 are postive, then the variance in K9 with the following formula would return a positive variance: =IF(ISERROR((C9-H9)/H9),"N/A",(C9-H9)/H9)

However

If both figures in C9 & H9 are negative, then the variance in K9 would still show a positive variance. How can i amend the formula in K9 to show me a Negative figure instead of a positive one?

ie: C9 = -114,422
H9 = -22484
K9 = -408.9%

 

[jasonb75]

What about when 1 is positive and the other is negative? This will cure 1 problem but might cause another.

=IFERROR((C9-H9)/H9*SIGN(C9),"N/A")

 

[molsviking]

@jasonb75 Yes, I also need to contend with that

 

[jasonb75]

So out of the 4 possible combinations, which ones are incorrect with the edited formula?

 

[molsviking]

I have tweaked your formula and this works: =IF(ISERROR((C9-H9)/H9),"N/A",(C9-H9)/H9*SIGN(C9)), alternatively, this also works: IF(ISERROR((C9-H9)/ABS(H9)),"N/A",(C9-H9)/ABS(H9))

Both formulas give the same answer with positive/negative signs.

It also works for a positive & negative figure.

Thanks again for your help

 

[jasonb75]

Your first one is just a longer version of my formula, you don't need to change IFERROR to IF(ISERROR unless you're using a version of excel that is ~13 years out of date.