Forum Discussion
Using Formulas (IFERROR?)
I am trying to figure out a way to use the same formula (or combined formulas we can use in each) that will return the % of change as either -100% if column B is 0 or 100% if column A is 0 but B is not.
The formula we are using right now is
IFERROR((-(E152-F152)/E152),"0.0"%)
This returns any columns with errors as 0, but it also is returning the column that should be 100% as a 0. Is there another formula I can combine or a better one to use that will do this?
| 2026 Budgeted | 2027 Proposed | % of Change |
| 6,565,464 | - | -100.0% |
| - | 140,388 | 0.0% |
| - | 50 | 0.0% |
3 Replies
Hello!
Your Formula is OK. Just insert 1 instead of "0.0"%. And apply the percentage format from the number group of the Home tab if is not applied yet.
Why are you trying to invent a new kind of percentage calculation?
The formula for percentage change is:
2027_Proposed / 2026_Budgeted - 1If you don't like the !DIV/0! error then amend the formula to this:
=IFERROR(2027_Proposed / 2026_Budgeted - 1,"not defined") =IF(2026_Budgeted=0;"not defined", 2027_Proposed / 2026_Budgeted - 1)Instead of "not defined" you can use a text more suited to your data, e.g. "no budget in 2026"
What sort of errors are you trying to catch to show 0%? because the simpliest thing would be to change the iferror from 0 to 100 but I agree that simpliest isn't always the best so try this:
=IFERROR(IF(E152,(F152-E152)/E152,1),0)
note I rearranged the equation to remove that negative sign and got rid of the text "0.0"% in favor of just 0 and let your formatting handle it. I also just passed a value of 1 for the "100%" value.
