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 inde...
Excel
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