Forum Discussion
John Richards
Mar 27, 2018Copper Contributor
Help with replacing #DIV/0! error with text
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 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
John,
=IFERROR(IF(H4<>"",(H4-G4)/G4,""),"N/A")
or
=IF(G4=0,"N/A", IF(H4<>"",(H4-G4)/G4,""))
Hi John,
You may use
=IFERROR(<your formula>,<your text/value if error>
- John RichardsCopper Contributor
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
- MefadsCopper Contributor
I would like a formula that returns 0% when both columns are zero, 100% when realized when target =0
YR2 (2019) Target Actual % - Deviation 78.00 595.00 ▲ 663% 0.00 0.00 0% 0.00 456.00 100% 0.00 105.00 100% 78.00 34.00 ▼ -56% 0.00 0.00 0%