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!
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.