An ugly % variance formula needs help to be pretty

[Nobleks]

I have an ugly formula for percent variance that I have been trying to clean up for a financial report, but I'm hitting walls with it. One big wall is making sure that google sheets likes it too.

I would love some help and insight (other than using C-4...I've already thought of that.)

Thank you,

[Limor_OP]

You can do it with conditional format.
mark your range-->go to format cells-->custom--> copy this 0;-0;;@

[Nobleks]

Unfortunately, it doesn't take into consideration that the powers that be "DEMAND" that "N/A"
.

[loginjmor]

Hi -

When I have several criteria that I am testing against, instead of hard coding those into a formula, I usually make a little look up table and use, you guessed it, VLOOKUP, or INDEX/MATCH to do the testing for me. It keeps a lot of those pesky IF statements out of there, and eliminates the sneaky logic errors you can run into with multiple, nested IF statements. So, over to the side of your table (or you could put it on a separate sheet) I have created a table for the various values of Column D versus Column G. So, for example, if I'm reading your formula correctly, if the value in Column D is <0, the answer is always NA. If D is 0 and G is >0, NA. If D > 0 and G is 0, NA. Otherwise, calculate the percentage of the two values (apparently if both are 0, you want 0 to display, not DIV/0!, so I added the IFERROR test to catch that). The last part is to check if it's outside the range of -9.999 to +9.999 which I just do an absolute value and test it once.

Anyway, the formula in H11 looks like:

=IFERROR(IF(VLOOKUP(D11,$L$10:$P$12,MATCH(G11,$L$9:$P$9),TRUE)="NA","NA",IF(ABS((D11-G11)/ABS(G11))>9.999,"NA",(D11-G11)/ABS(G11)))*C11,0)

Where $L$10:$P$12 is my little look up table. Attached is your spreadsheet with everything above included. One other note, using a table, rather than hardcoding your test paramaters, allows you to change those parameters easily a year later after you've forgotten what you put in there and what all formulas reference those parameters. Just makes your life easier later. I would also suggest you put your 9.999 testing criteria in a cell, rather than coding into your formula. I intentionally didn't do that as I didn't want to muddy the waters more than I have.

Hope this helps.

[Nobleks]

That makes the most sense, let's see if the powers that be are interested.

[loginjmor]

Thanks for the rep!