Forum Discussion
AVERAGE from several sheets when data is ">0"
Hi, I have many sheets with the same layout and formulas. I want to find the average of all numbers from a specific row, in all the sheets. Problem is, many of the cells contain the number "0", and I want the formula to only choose values that are ">0".
I found that average for only one sheet works with this formula: =AVERAGEIF(SHEET1!R2:R30;">0")
And it works for several sheets without the IF kriteria:
=AVERAGE(SHEET1:SHEET5!R2:R30)
I have tried this:
=AVERAGEIF(SHEET1:SHEET5!R2:R30;">0")
But this only returns #VALUE, so there must be some error, and I just can't find out what it is.
Can anyone help? I would be so thankful if anyone would have a solution for this!
Best regards
Sissel
I found that average for only one sheet works with this formula: =AVERAGEIF(SHEET1!R2:R30;">0")
And it works for several sheets without the IF kriteria:
=AVERAGE(SHEET1:SHEET5!R2:R30)
I have tried this:
=AVERAGEIF(SHEET1:SHEET5!R2:R30;">0")
But this only returns #VALUE, so there must be some error, and I just can't find out what it is.
Can anyone help? I would be so thankful if anyone would have a solution for this!
Best regards
Sissel
2 Replies
As you have found, AVERAGEIF does not work across multiple sheets; nor do COUNTIF, COUNTIFS, SUMIF etc.
Here is a solution using a helper column:
Select all five sheets by clicking on the sheet tab of Sheet1, then Shift-clicking on the sheet tab of Sheet5.
Insert an empty column in column S.
Enter the following formula in S2:
=--(R2>0)
Fill down to S30.
Now select the sheet where you want to compute the average.
Enter the formula
=SUM(Sheet1:Sheet5!R2:R30)/SUM(Sheet1:Sheet5!S2:S30)
(You can use another column as helper column if you wish; adjust the final formula accordingly)
- Thank you, that's a good idea! And that gave me some more ideas😊
I ended up extracting all numbers over 0 to the helper column. And then use the AVERAGE formula. Then I can also use MEDIAN an other similar formulas on the column.
