• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Josiev

New Member
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.

75559

Any help is very much appreciated.

Regards,
Josie
 

Attachments

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
 
Another possibility I have toyed with in the past is to report positive and negative deviations as percentages of a different base value.
Code:
= (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'.]
 
Back
Top