Need Excel Formula to calculate % change when Base value is extremely small

[Josiev]

Hello,

I have looked to check solution for my problem but could not find a good answer. Hoping to get solution with help from this source.

Below is a sample of what I mean. The Budget values come from our financial system which were loaded by FP&A team. However, many values that should have been zeroed out, were left with very small values. I typically use the ABS formula to calculate % change to achieve proper positive/negative results. Since the base value has extremely low value, it generated a long result, see below. I would like to find a way that generates blank or "n/a" results but I do not know how to insert this statement to the myriads of actual versus budget expense accounts where this formula needs to be applied. I have attached the excel workbook to show the formula I tried to use but to no avail.



Any help is very much appreciated.

Regards,
Josie

 

[pecoflyer]

Hi
please define "small values"?

 

[Josiev]

Hi pecoflyer,

Small values are numerical values less than 0.001.

Thank you,
Josie

 

[Hui]

F3: =IF(OR(C3=0,D3<=0.001),0,E3/D3)
copy down

 

[Josiev]

Thank you Hui...I kept the ABS formula to the end as there are values that need to have the right negative / positive % result but added your suggestion on D3<=0.001...that seems to work.

Regards,
Josie

 

[Peter Bartholomew]

Another possibility I have toyed with in the past is to report positive and negative deviations as percentages of a different base value.

= (Budget-Actual) / (IF(Budget>Actual,Budget,Actual))

For small deviations the difference is barely perceptible but, in situations like this, the negative percentage change reported is limited to (100%).

[Note: My expression for MAX is cumbersome but that is because it is designed to work with arrays 'actual' and 'budget'.]