• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Applying IFERROR [SOLVED]

F

Fendtasia

New Member
I don't want the #DIV/0! to show up in my total columns. Here is the situation: There are 6 sections of columns, with 4 columns in each section. For this example, say the 4 columns are A, B, C & D. Each of these is used to rate (from 1 to 5) a different aspect of a unit on 6 (sections) different dates. There are 100 units (rows). The four total columns show the average of A over 6 dates, B over 6 dates, etc. However, not all rows have data in them. In these total columns, the #DIV/0! error message is showing up. I want those cells to be blank. I just can't seem to apply the IFERROR correctly. Is this even the right formula to be using in my case?

The only other information, may or may not be relevant, is that the total columns are formulated to be color-filled depending on the answer. (1 = no fill, 2 = green fill, 3 = yellow fill, 4 = orange fill and 5 = red fill.

Thank you for your help with this - I've tried very hard to solve it on my own. Fendtasia
 
Hi, Fendtasia!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


There are at least 2 ways of avoiding a #DIV/0! error:


a) Checking the divisor

Instead of this:

=A1/A2

do this:

=IF(A2=0,"",A1/A2)


b) Using IFERROR (2007+) or IF & ISERROR (2003-)

Instead of this:

=A1/A2

do this:

=IFERROR(A1/A2,"")

=IF(ISERROR(A1/A2),"",A1/A2)


Replace the "" for zero or any value you want to get displayed.


Regards!
 
Another possible one


=IF(OR(A1=0,A2=0),"",A1/A2)


Or this if you want to display zero's


=--IF(A2,A1/A2)
 
@oldchippy

Hi!

Nice one the last, I've never saw it. But why the OR with A1=0 in the first one? It'd retrieve zero anyway.

Regards!
 
Thank you for the tips posted above. However, I guess I'm not as adept at Excel as I thought! I have Excel 2010 and have copied the link below to my worksheet in Drop Box.

If there are any questions after reviewing my spreadsheet, please feel free to ask. My objective is to not have the #DIV/0! error show when there is no data to average. This is the master sheet so I need the four average total columns to all have the formula. I appreciate any and all help! Thank you, Fendtasia


https://dl.dropboxusercontent.com/u/27274593/Housing_Status-IvesManor2013Master-1stQ%20(Repaired).xlsx
 
Now that I have corrected my spreadsheet according to the quick answer I received last month, I've found that when the average is not a whole number, the color scheme doesn't work. ie. if the average is 2, color of cell is green, 3 is yellow, 4 is orange, and 5 is red.

So I need to be sure the average in the answer columns is rounded up or down to the nearest whole number. How do I add =round to the formula? Or is there another way? An example of the formula as it stands is: =IFERROR(AVERAGE(C2,J2,Q2,X2,AE2,AL2),0)

I appreciate any and all help! Fendtasia
 
You must log in or register to reply here.
Back
Top