# Average same cell on multiple sheets excluding blank

#### Grinch

##### New Member
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?

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
This should exclude blanks.

=AVERAGE('1:22'!F2)

It doesn't exclude zeros.

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

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)

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

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

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.

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

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)

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.

Replies
3
Views
161
Replies
9
Views
71
Replies
4
Views
61
Replies
2
Views
187
Replies
7
Views
120

1,206,949
Messages
6,075,811
Members
446,158
Latest member
octagonalowl

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back