Averaging across multiple sheets with#div0 errors

  • Thread starter Thread starter NatFIsker
  • Start date Start date
N

NatFIsker

I'm trying to create a summary sheet, that averages the value of 1 cel
on each of 6 consecutive sheets (all in a single workbook). For mos
sheets, however, there will not be data added until the end of th
week; thus there are div0 errors, and in some cases, 0 values, both o
which then, of course, block the overall summary.

Tried to use conditionals to resolve this, i.e.:
=AVERAGE(IF(Monday!B5>0, Monday!B5), IF(Tuesday!B5>0, Tuesday!B5)
IF(Wednesday!B5>0, Wednesday!B5, ""))

But if the Wednesday!B5 = 0, I get a value error.
If Wednesday!B5 = #div0, I get a div0 error.

Anway I can have an average of a sequence of cells on different sheets
where 0 is a valid value, and div0 error values are ignored and no
counted?

Thanks in advance
 
Ugly but works:
=AVERAGE(SUMIF(Monday!B5, "<>#DIV/0!", Monday!B5), SUMIF(Tuesday!B5
"<>#DIV/0!", Tuesday!B5), SUMIF(Wednesday!B5, "<>#DIV/0!"
Wednesday!B5), SUMIF(Thursday!B5, "<>#DIV/0!", Thursday!B5)
SUMIF(Friday!B5, "<>#DIV/0!", Friday!B5), SUMIF(Saturday!B5
"<>#DIV/0!", Saturday!B5))


Thanks - found a solution that could be cobbled together from variou
threads on site
 
One way

=SUM(Monday:Saturday!B5)/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Monday";"Tuesday"
;"Wednesday";"Thursday";"Friday";"Saturday"}&"'!B5"),">0"))

assuming that the sheets are named after Mon-Sat
 
Using functions that reference multiple worksheets is
tricky. I'd suggest keeping it simple and use another cell
on each sheet to evaluate B5 (say B6), then average B6
across all sheets.

So in B6 in each sheet use:

=IF(ISNUMBER(B5),IF(B5>0,B5))

And then average:

=AVERAGE(Monday:Wednesday!B6)

HTH
Jason
Atlanta, GA
 
Oops. premature. Doesn't work, as the SUMIFs resolve to 0 for 'div0
errors, meaning the calcuation is off. Help
 
Nat

You can get something like this by selecting the sheets
Mon to Wed and typing in the formula

=IF(ISNA(Sheet1:Sheet3!B5),"",AVERAGE(Sheet1:Sheet3!B5))

This workks on the default workbook of 3 sheets.

regards
Peter
 
Hi Peo. Nice, but it'll fail if any B5 contains #DIV/0!,
which the OP stated as a possibility.

Jason
 
Thanks for the help Peo and Jason.

Peo: your solution leaves me with a div/0 error. The formula evaluate
perfectly for the latter part, coming up with the correct count o
data. However, the first part (SUM(Monday:Saturday!B5), when confronte
with div/0 due to data not yet entered, sums to div/0.

Jason, isnumber resolves to FALSE for blank cells, which still throw
the averages off. Also, '0' is a valid value; a blank cell should b
ignored.

Or are blank cells being evaluated as 0s
 
Nat

I looked at the other posts and my own. This seems to work

=IF(ISBLANK(Sheet1:Sheet3!$B$5),"",AVERAGE(Sheet1:Sheet3!
$B$5))

use the pointing method to get the name of your sheets.

regards
Peter
 
Thought it was solved, this time, using Peo's clever count for th
second half and my own ugly bit for the first half:

=SUM(SUMIF(Monday!B5,"<>#DIV/0!",Monday!B5),SUMIF(Tuesday!B5,"<>#DIV/0!",Tuesday!B5),SUMIF(Wednesday!B5,"<>#DIV/0!",Wednesday!B5),SUMIF(Thursday!B5,"<>#DIV/0!",Thursday!B5),SUMIF(Friday!B5,"<>#DIV/0!",Friday!B5),SUMIF(Saturday!B5,"<>#DIV/0!",Saturday!B5))/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}&"'!B5"),">0"))

But the sumproduct/countif part isn't counting cells with value=0.

hmm. Almost there
 
Try this amendment

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Monday";"Tuesday";"Wednesday";"Thursday";"F
riday";"Saturday"}&"'!B5"),"<>#DIV/0!",INDIRECT("'"&{"Monday";"Tuesday";"Wed
nesday";"Thursday";"Friday";"Saturday"}&"'!B5")))/SUMPRODUCT(COUNTIF(INDIREC
T("'"&{"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}&"'!B5"
),">0"))
 
Peo - Cheers, Mate. The Amendment worked for me, modified ever s
slightly:
=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}&"'!B5"),"<>#DIV/0!",INDIRECT("'"&{"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}&"'!B5")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}&"'!B5"),">=0"))

(just changed the final criterion from >0 to >=0)

Peter - I tried your method of multiple tab select, but then foun
myself unable to insert the formula in a cell on a given tab, rathe
than some sort of cross-worksheet template. Perhaps I'm messin
something up in your method. Thanks for the help, though.

-
 
Hi

Sometimes you have to walk away. The error is in the
average so

=IF(ISERROR(AVERAGE(Sheet1:Sheet3!B5)),"",AVERAGE
(Sheet1:Sheet3!B5))

Hope you see the post!

email (e-mail address removed)
regards
Peter
 
=SUM(Sheet1:Sheet3!A1)/COUNT(Sheet1:Sheet3!A1)

will produce the average of all non blank entries in cell A1 betwee
sheet 1 and sheet 3

Hope this helps
Pete
 
Back
Top