Forum Discussion

Denis_Starr0603's avatar
Denis_Starr0603
Copper Contributor
Mar 07, 2024

Averaging non sesquential cells in Excel

Hi, I'm trying to average non sequential cells in Excel to compare food shopping between 2 shops I use approx. a week apart. For example, fruit and vegetables from a chain Supermarket against an independent retailer. For the Supermarket, I've used =AVERAGE(E11:E12:E14:E16:E18:E20) and for the independent retailer =AVERAGE(E13:E15:E17:E19). For another food item (in different stores) in a separate Column, I've used the same formula above but with G preceding the numbers. All cells have values listed.

 

When I manually add the numbers and divide by the number of weeks, I get totally different averages.

 

Any ideas?

excel

2 Replies

Sort By
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 07, 2024

    Denis_Starr0603 You might think this notation with all colons works as the groups of adjacent cells seem to be correctly separated. See picture below:

    But it doesn't. It results in an average for the range E11:E20, this inclusive of E13 and E17. Why? I don't know. It should have raised an error of some kind. The fact that it doesn't could be a bug.

     

    Separate each group of adjacent cells with a comma (or a semi-colon if that is the default list separator in your Excel set-up) and it should work as expected. 

    =AVERAGE(E11:E12,E14:E16,E18:E20)
    • Denis_Starr0603's avatar
      Denis_Starr0603
      Copper Contributor
      Mar 08, 2024

      Riny_van_Eekelen 

      Thank you Riny_van_Eeelen.

      I found the following worked =AVERAGE(E11:E12,E14,E16,E18,E20) Also same formula for Column G.

      Also =AVERAGE(E13,E15,E17,E19) Also same formula for Column G

       

      Only E11 and E12 (similarly G11 and G12) are sequential hence E11:E12, (same for Column G) with commas used between other cells. If maybe I have 2 or more sequential cells in future, eg E23:E26, etc should work.

       

      I've manually added the relevant cells and divided by the number of weeks used and the Excel formula above works.

       

      Thank you again-much appreciated.   

Resources