Percentage of a Goal / Actual with Negative Numbers

Hey Everyone,

Having issues figuring this one out. I need to show what percentage of a number something is while possibly having 1 or both numbers negative.

My goals column start in Z5 and the actuals are in AA5 (the actuals that correspond to the goal are on the same row as one another). I used this for another column but does not seem to work consistently.

=1+(((W5-V5)/V5)*SIGN(V5))

Your help is appreciated.

Best,
Ryan

To clarify the goal may be for example -50,000 and -50,000 would be 100% of goal.

Hi -

You could use absolute values of the numbers and calculate the percentages that way. I assume all of the numbers would have to be of the same sign (e.g, negatives with negatives and positives with positives). I'm not sure what the meaning of a -50 and +100 would be relative to a percentage as those two numbers would only describe the boundaries of a range.

So your formula might be =ABS(V5)/ABS(W5). If V5 is -6 and W5 is -10 then the result is 0.6 (or 60%). It works the same with positive numbers.

Not sure if I answered your question, but I hope that helps.