OK, There has to be a way...
Posted by Deirdre on August 01, 2001 7:09 AM
I have created a spreadsheet for calculating daily totals. This spreadsheet will change daily and is not cumulative from the day before.
My problem is I cannot predict whether I will have 3 entries to calculate or 20. Therefore I have set up the worksheet with 20 rows of possible data to calculate.
Now, on the days I do not have data in say rows 10-20, I am returned the #DIV/0! error. Normally I can overlook this annoyance, BUT it interferes with my AVERAGE total at teh bottom of the worksheet. If the #DIV/0! errors are present in a particular colum, it will not calculate the average of that column.
My questions:
1) Is there a way to leave my formulas in the cells but have the blank rows return a blank cell (not a #DIV/0! error)
2) If there isn't a way to avoid the #DIV/0! , how do I set it up to calculate an average for a colums that contains a #DIV/0! ?
Reminder: The #DIV/0! error I am receiving is not due to a #/0 error, but a simple blank cell.
Thanks much for your time.
Posted by Mark W. on August 01, 2001 7:15 AM
I'm confused!!! What is the formula that's
causing #DIV/0! and are you using AVERAGE() or
are you averaging by using SUM()/COUNT()?
Posted by Dax on August 01, 2001 7:20 AM
Yes, there is...
=IF(ISERROR(AVERAGE(A1:A20)),"",AVERAGE(A1:A20))
HTH,
Dax.
Posted by Deirdre on August 01, 2001 7:29 AM
In rows I do not have data in on a particular day, it will return the #DIV/0! error because it is empty. The #DIV/0! error is usually a result of say L10=J10/K10, in which J10 and K10 are empty for this particular day.
Now at bottom of spreadsheet I want to average Column L. However, because L10-L20 have #DIV/0! errors due to empty cells, it will not average the data in L1-L9. I was using the AVERAGE () function.
Hope this helps.
~Deirdre
Posted by Mark W. on August 01, 2001 7:35 AM
Okay, since AVERAGE() ignores text then instead of
=J10/K10 use =IF(K10,J10/K10,"").
Posted by Deirdre on August 01, 2001 9:07 AM
Thank you all for the suggestions, but I must still be doing something wrong.
I input the formula as suggested above. However when I do so, it prompts a "Circular" reference dialog box. Further, when I input values for the cells (I6 and C6), it returns 0.
The actual data from my spreadsheet:
=IF(K6,I6/C6,"")
The original formula was =I6/C6, which of course returned a #DIV/0! error when I had no data in it to calculate. I simply want it to return a blank cell, and not show the #DIV/0 error so when I average I1:I20, I will get a value even if I9:I20 may be blank on certain days.
:: raspberries at Excel ::
Posted by Aladin Akyurek on August 01, 2001 10:56 AM
Change this to: =IF(C6,I6/C6,"")
