Average same cell on multiple sheets excluding blank

[Grinch]

sheets are named 1 through 22, the cell is f2 so I was trying:

=AVERAGE('1:22'!F2)/COUNTIF('1:22'!F2,">0")

but I come up with a #VALUE!

any ideas?

 

[AlphaFrog]

This should exclude blanks.

=AVERAGE('1:22'!F2)

It doesn't exclude zeros.

 

[Grinch]

my bad... i actually need the average excluding 0's.

 

[Aladin Akyurek]

sheets are named 1 through 22, the cell is f2 so I was trying:

=AVERAGE('1:22'!F2)/COUNTIF('1:22'!F2,">0")

but I come up with a #VALUE!

any ideas?

Try...

=SUM('1:22'!F2)/INDEX(FREQUENCY('1:22'!F2,0),2)

 

[Grinch]

YESSS!!!!!! Thank you!!!!!!!!!!

 

[Drew_]

Why the number 2 at the end? I am having this same issue but this formula did not work for me. i have sheets titled 1 through 60, i want to the put my formula in cell R3 on a master sheet titled MASTER, I want to pull data from cell N7

 

[Aladin Akyurek]

Why the number 2 at the end? I am having this same issue but this formula did not work for me. i have sheets titled 1 through 60, i want to the put my formula in cell R3 on a master sheet titled MASTER, I want to pull data from cell N7

In R3 of MASTER enter:

=SUM('1:60'!N7)/INDEX(FREQUENCY('1:60'!N7,0),2)

If you select the FREQUENY bit in the formula balk and hit F9, you'll see something like:

{2;8}

which means there are 2 number <= 0, 8 numbers > 0.

The 2 informs INDEX to pick out 8, the second number from the array the FREQUNCY function returns.

 

[Drew_]

Yes I entered that exact formula and it is returning "0". I have percentages in N7 of sheets 1-3 and I want to average that cell for all 60 sheets. Not sure why I am getting 0 as answer to this formula

 

[Aladin Akyurek]

Yes I entered that exact formula and it is returning "0". I have percentages in N7 of sheets 1-3 and I want to average that cell for all 60 sheets. Not sure why I am getting 0 as answer to this formula

Go to the formula bar, select the FREQUENCY bit, i.e. FREQUENCY('1:60'!N7,0), and hit F9. Copy what you see and post that here.

Also, what does this give?

=SUM('1:60'!N7)

 

[Drew_]

Silly me, it was showing zero because I had not converted it to a percentage like I wanted. The formula was correct and worked for me, thank you!

I now have another question...I want to create a graph from my data that automatically updates with new entries and also ignores 0's. The cells with 0's in them have formulas and are returning a 0 value because entries have not been put in yet. I converted the 0's to "NA()" and it seems to have worked but the graph does not update automatically. any thoughts?

Thanks.