Forum Discussion
Multiple #DIV/0! and #VALUE! errors.
Hi,
I'm new to the community and currently self teaching on excel, which is a both rewarding and frustrating. I have decided to post and ask the question as i cannot for the life of me figure out where i am going wrong here. I would like all the cells showing these errors to appear blank, unfortunately i have hit a brick wall and admitted defeat.
Can some one please help me tidy this up?
I obviously do not fully understand the IF command, though the calculations work exactly as they should with the data entered i would still like the fiinished spreadsheet to not show all of those errors.
I'm just looking at the spreadsheet.
M7 has a formula that will return a number or an empty string. Your formula only checks for M7=0 and an empty string "" is not equal to 0.
You have the IF thing down pat, but you need to apply the correct conditions.
I don't think wrapping everything into IfError is the best approach, since it will mask all errors and you won't know if something is really wrong in the spreadsheet. Error messages are helpful to find bugs.
Change the formula in O7 to check for a "" instead of a 0:
=IF(M7="","",(H7/L7)/M7)
In P7, I think you are not closing the brackets correctly:
=IF(H7=0,"",(H7-G7))/H7
I think what you really want is
=IF(H7=0,"",(H7-G7)/H7)
You can avoid the error of the Average formulas in row 17 by wrapping it in an IF that checks if the cells above have numbers, like
=IF(COUNT(M7:M16),AVERAGE(M7:M16),0)
If you don't want to show the zero, you can format it away with custom format.
File attached.
4 Replies
Hello,
your formula should work, at least it works fine in my tests. I only get a Div error if M7 is not empty, for example if it contains a space. Check that the cells in column M are truly empty.
I'm just looking at the spreadsheet.
M7 has a formula that will return a number or an empty string. Your formula only checks for M7=0 and an empty string "" is not equal to 0.
You have the IF thing down pat, but you need to apply the correct conditions.
I don't think wrapping everything into IfError is the best approach, since it will mask all errors and you won't know if something is really wrong in the spreadsheet. Error messages are helpful to find bugs.
Change the formula in O7 to check for a "" instead of a 0:
=IF(M7="","",(H7/L7)/M7)
In P7, I think you are not closing the brackets correctly:
=IF(H7=0,"",(H7-G7))/H7
I think what you really want is
=IF(H7=0,"",(H7-G7)/H7)
You can avoid the error of the Average formulas in row 17 by wrapping it in an IF that checks if the cells above have numbers, like
=IF(COUNT(M7:M16),AVERAGE(M7:M16),0)
If you don't want to show the zero, you can format it away with custom format.
File attached.
Thank you! It is so much simpler when explained properly! Trying to pick this up from bits and pieces on the internet can be extremely frustrating! Thanks again, great advice and meticulously explained!!
John,
It's easy and simple!
Just surround all that formulas with https://support.office.com/en-us/article/IFERROR-function-c526fd07-caeb-47b8-8bb6-63f3e417f611See how it works in the attachment file.
