Percent Change Error Trap
To keep the percent change formula from returning errors and/or extreme values, consider a nested IF statement to trap errors or values that fall outside of a certain range. In the example below, "n/a" refers to "not applicable". Errors have been trapped in lines one through three. In line four, the formula returns "n/a" because the absolute value of the change is greater than 200%. This logic is written into the formula to prevent it from returning exteme values.
The formula in G4 is below. It is copied from cell G4 into cells G6, G8, G10, and G12.
=IF(OR((E4=0),(F4=0)),"n/a",IF(ABS(F4/E4-1)>2,"n/a",(F4/E4-1)))
Posted on Friday, April 11, 2008 |
Comments (2) |
Permalink
The second IF looks incomplete… it has greater than, without an argument.
If the error you get in the first if is a div by zero, you can simplify by using =IF(F4=0,“n/a”, etc.