Forum Discussion
Averaging the same cell across multiple sheets
So I'm trying to reference cell F2 across multiple sheets. My formula is as follows:
=AVERAGE(January:December!F2)
The value that pops up is 4.36 but it should be 4. Within Excel, there's a help function and they basically tell me to use the same format that doesn't work so I'm at wit's end with this.
Essentially, the total number of F2 across all cells is 48, which divided by 12 should be exactly 4 and not 4.36.
UPDATE: If you divide 48 by 11, you'll get the number I'm getting in my averaging screen. However, I triple checked that I have 12 tabs referenced so I'm still puzzled at the moment.
12 Replies
Another way is to use helper cell in each of monthly sheets. Let say G2, but it could be in any row/column and you may hide it if needed. Select at once all sheets from Jan to Dec and type in G2
=IF(F2,F2,"")and after that to average all of them
=AVERAGE(January:December!G2)AVERAGE() ignores all cells with empty texts.
- This puts an unnecessary step,,, to skip blank cells I've shown this in my first post,,
AVERAGE(IF(N(INDIRECT("'"&$A$1:$A$12&"'!F2"))>0,Quite often helper cells/ranges save a lot of time on maintenance phase compare to variants where raw data/data preparation/report mixed in one.
- Are those sheets in order? More importantly, looking left to right, is January the left-most sheet of the series of months and December the right-most sheet, with NO sheets in-between?
- Correct. January is the left most sheet and December is on the furthest right. The sheets in between are the months that come between January and December of the same year so Jan, Feb, March, Apr, May, etc.
Like I said, the issue was in Feb where the data I needed was actually in G2. Formatting error so all of the data was offset to the right by one. Once I fixed that, the formula is working correctly.
I appreciate your contributions.
I came to find out it was user error lol (surprise surprise). Since I knew one of the months was not accounted for, I used a sum function across all sheets which came out to be 48, as expected. So, I just went into each month and altered the value to see if it would alter my SUM function.
One month didn't and that was February because I had a slightly different format where the data I wanted from that month was spaced one cell to the right. After fixing it, the value is coming up as correct.
Anyway, I apologize on sending you guys on a wild goose chase. Not sure why I didn't think of trying this earlier.
No need to apologize, it can happen to all of us. I'm glad that you found the cause of the problem.
Nothing wrong with the formula you are trying so far. Since you are handling so many TABs therefore I would like to suggest a better method, is faster also.
- Store Sheet/TAB name in any blank Range,, and use this array (CSE) formula to get Average.
{=AVERAGE(IF(N(INDIRECT("'"&$A$1:$A$12&"'!F2"))>0,N(INDIRECT("'"&$A$1:$A$12&"'!F2"))))} N.B. Range A1:A12 contents Sheet/TAB name.Finish formula with Ctrl+Shift+Enter.
Rajesh_Sinha Please don't take this personally, but I frown upon using the INDIRECT function, for two reasons:
1. the function is volatile and calculates on any change in your file
2. formulas using INDIRECT are notoriously difficult to understand by others.
JKPieterse ,,
I can understand your concern,, since Ayeziza is trying to access value from 12 different sheets,, in that case the show method is best suitable,, yes is a volatile but here specially reading Sheet Name only so hardly makes any difference,, to avoid this you can assign, a NAME to range A1:A12 (has name of 12 sheets) and use that in Formula like ,,
Replace "'"&$A$1:$A$12&"'!F2" with "'"&TabName&"'!F2"Here TabName is "Named Range" given to A1:A12 has name of 12 sheets.
Hi Ayeziza ,
With the information you told me, I tried to make the sample file and =AVERAGE (January: December! F2) function properly.
If it doesn't look what you mean, it would be better if you could include files to clarify what you want.Regards,
Sony
AVERAGE doesn't count empty cells. I suspect that one of the 12 sheets has a blank in F2. That would cause Excel to divide the sum 48 by 11 instead of by 12.
48/11 = 4.363636...
If you really want to divide by 12, you should either enter a 0 in the blank F2, or use
=SUM(January:December!F2)/12
