Forum Discussion
Need Help on stopping the #DIV/0! error.
I am at my wits end. I have been trying to get this to work for hours.
What I need is to have cell I15 evaluate cell H15.
If H15 has a value equal to or greater than 0.00 then to calculate the average of H15 otherwise leave leave I15 BLANK or 0.
Then I need to have cell I16 evaluate cells H15 and H16.
If H15 and/or H16 has/have a value equal to or greater than 0.00 then to calculate the average of H15 and H16, otherwise leave leave I16 BLANK or 0.
And so one through cell I26...keeping in mind that cells H15 thru H25 could possibly have a value of BLANK due to the data populated to these cells.
I have figured out how to do the averaging of these cells...my issue is that I cannot figure out how to get rid of the #DIV/0! error result in the cells I15, etc, when the value in H15, etc is BLANK.
The reason for these cells having a value of BLANK is because the workbook I created is to track teller differences and set scoring for annual performance reviews. I can't have the worksheet average into the calculation the months the individual was not employed. This will skew the person's average at the end of the year resulting in an incorrect performance score.
See the attachment for the formula being used in each cell.
I would appreciate any assistance anyone can lend with this.
Hi there,
There are two ways to solve this problem.
1 - You must change blank cell into 0. = =IF(AVERAGE(A2>=0),AVERAGE(A2), "")
In this case your formula will work perfect.
2 - If you don't want to change, then use Iferror function. - =IFERROR(IF(AVERAGE(A2>=0),AVERAGE(A2), ""),"0")
9 Replies
SergeiBaklan Thank you so very much for your help and responding. 🙂
Hi there,
There are two ways to solve this problem.
1 - You must change blank cell into 0. = =IF(AVERAGE(A2>=0),AVERAGE(A2), "")
In this case your formula will work perfect.
2 - If you don't want to change, then use Iferror function. - =IFERROR(IF(AVERAGE(A2>=0),AVERAGE(A2), ""),"0")
Ilgar_Zarbaliyev Thank you very much. The second suggestion is exactly what I needed to fix the error. The first wouldn't have worked with the results I needed because the monthly average that would result in a "blank" I cannot have averaged into the YTD Rolling Average. The second takes into account that the Blank would not be counted into the calculation, and still clears the error.
I appreciate your assistance with this. You are a lifesaver!
Thank you for your best response mark
Your tests are based upon the AVERAGE to date but you should not be testing against 0, you should be testing for an error using ISERROR or ISNUMBER. Better, base the test on the COUNT
= IF( COUNT(ValuesToDate), AVERAGE(ValuesToDate), "" )
- I appreciate your help and time in responding. I appreciate it very much 🙂
You will need to apply an IFERROR function to the start of your formula.
Although the IF statement is correct, excel registers the BLANK as "" and not as an integer, which is why the >=0 returns an error.
You're adjusted formula would be
=IFERROR(IF(AVERAGE(....""),)- I appreciate your help and response. Thank you very much 🙂
