SOLVED

Help with replacing #DIV/0! error with text

Copper Contributor

Good morning all

 

I am currently working on a spreadsheet to show trends in relation to various incidents I deal with at work. I am looking to track changes in the number of incidents along with the percentage increase/decrease on a weekly basis but have come across a #DIV/0! error that I cannot solve (self taught newbie to excel). 

 

Error exampleError example

The formula currently used to work out the percentage change from the previous week is

=IF(H4<>"",(H4-G4)/G4,"") and I am fully aware that the error is being caused because G4=0.  I would like the cell to show some text such as "N/A" instead of the #DIV/0! error but am struggling to find the solution even with the help of Google! I have attached an example of the spreadsheet if that will help.

 

Any assistance is appreciated

5 Replies

Hi John,

 

You may use

=IFERROR(<your formula>,<your text/value if error>

Hi Sergei, thank's for the quick response

 

sorry but I'm a complete beginner with excel.  Could you show me how to change my current formula =IF(H4<>"",(H4-G4)/G4,"") to use =IFERROR as you have described as I keep getting errors such as 'too many arguments for this function. 

 

thanks

best response confirmed by John Richards (Copper Contributor)
Solution

John,

=IFERROR(IF(H4<>"",(H4-G4)/G4,""),"N/A")

or

=IF(G4=0,"N/A", IF(H4<>"",(H4-G4)/G4,""))

 

 

 

Sergei you are a genius.  I have been trying to solve this for two days!  Thank you very much for your help.  

@John Richards 

I would like a formula that returns 0% when both columns are zero, 100% when realized when target =0

YR2 (2019)
TargetActual% - Deviation
78.00595.00 ▲ 663%
0.000.00 0%
0.00456.00 100%
0.00105.00 100%
78.0034.00 ▼ -56%
0.000.00 0%
1 best response

Accepted Solutions
best response confirmed by John Richards (Copper Contributor)
Solution

John,

=IFERROR(IF(H4<>"",(H4-G4)/G4,""),"N/A")

or

=IF(G4=0,"N/A", IF(H4<>"",(H4-G4)/G4,""))

 

 

 

View solution in original post