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?

 

[Aladin Akyurek]

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!

You are welcome. Thanks for the update.

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.

You might want to try using an Excel table (the Insert Table option from the ribbon).

You can then create an additional column with a formula in it that says:

IF(ISNUMBER(1/C2),C2,NA())

 

[Drew_]

I don't understand why I need that formula. My current IF formula contains a VLOOKUP for the data I want and if it contains a 0 I told the IF formula to return NA() and it's done that, it works great. I just cannot get the chart to update automatically. I've named my range of data and created the chart that way, but it still won't update automatically.

 

[Aladin Akyurek]

I don't understand why I need that formula. My current IF formula contains a VLOOKUP for the data I want and if it contains a 0 I told the IF formula to return NA() and it's done that, it works great. I just cannot get the chart to update automatically. I've named my range of data and created the chart that way, but it still won't update automatically.

Care to post that formula containing VLOOKUP?

 

[Drew_]

=IF(VLOOKUP(Totals!F2,Totals!$F$1:$I$34,1,)=0,NA(),(VLOOKUP(Totals!F2,Totals!$F$1:$I$34,1,)))

I have this formula across 3 columns, pulling data from 3 columns on the Totals sheet. Whenever a new entry is entered on the Totals sheet, I want my graph to update automatically on a separate sheet (the one that contains the above formula)

 

[Aladin Akyurek]

Care to explain what this is doing?

VLOOKUP(Totals!F2,Totals!$F$1:$I$34,1,)

 

[Drew_]

Yeah I was pulling the number from the 2nd row, F column, out of a 4 column range and I wanted the number from the first column (F). However, it was not pulling the correct numbers from each column. So I changed the formula to =INDEX(Totals!F2:F61,MATCH(Totals!F2,Totals!F2:F61,0)) and started getting the right numbers and now my chart is updating automatically!

 

[Aladin Akyurek]

Yeah I was pulling the number from the 2nd row, F column, out of a 4 column range and I wanted the number from the first column (F). However, it was not pulling the correct numbers from each column. So I changed the formula to =INDEX(Totals!F2:F61,MATCH(Totals!F2,Totals!F2:F61,0)) and started getting the right numbers and now my chart is updating automatically!

I'm still in dark...

Is this not what you are looking for?

=IF(Totals!F2,Totals!F2,NA())

 

[Drew_]

That may have been an easier way to do it. I didn't think of that because I was trying to pull data from a different sheet. But the INDEX and MATCH formula seems to have worked! Thanks for your help