BrianMH
04-28-2009, 08:53 AM
I have a workbook with 4 sheets. I am currently getting an average for all the cells in column E on each of these sheets with the current formula. I want an actual average of all the cells that are not 0 and not blank. I don’t want an average of the averages of each sheet if you know what I mean. This formula works but I have a feeling there is a simpler way to do it. Any ideas?
=SUM(SUMIF('Sheet1'!E:E,"<>0"),SUMIF('Sheet2'!E:E,"<>0"),SUMIF('Sheet3'!E:E,"<>0"),SUMIF('Sheet4'!E:E,"<>0"))/SUM(COUNT('Sheet1'!E:E)-COUNTIF('Sheet1'!E:E,0),COUNT('Sheet2'!E:E)-COUNTIF('Sheet2'!E:E,0),COUNT('Sheet3'!E:E)-COUNTIF('Sheet3'!E:E,0),COUNT('Sheet4'!E:E)-COUNTIF('Sheet4'!E:E,0))
=SUM(SUMIF('Sheet1'!E:E,"<>0"),SUMIF('Sheet2'!E:E,"<>0"),SUMIF('Sheet3'!E:E,"<>0"),SUMIF('Sheet4'!E:E,"<>0"))/SUM(COUNT('Sheet1'!E:E)-COUNTIF('Sheet1'!E:E,0),COUNT('Sheet2'!E:E)-COUNTIF('Sheet2'!E:E,0),COUNT('Sheet3'!E:E)-COUNTIF('Sheet3'!E:E,0),COUNT('Sheet4'!E:E)-COUNTIF('Sheet4'!E:E,0))