Forum Discussion

Ayeziza's avatar
Ayeziza
Copper Contributor
Oct 26, 2020

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 h...
excel
Formulas and Functions
Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Oct 26, 2020

Ayeziza 

 

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.

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Oct 26, 2020

    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.

    • Rajesh_Sinha's avatar
      Rajesh_Sinha
      Iron Contributor
      Oct 27, 2020

      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.

                               

Resources