Averaging non sesquential cells in Excel

[Denis_Starr0603]

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?

[#2]

@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)

[#3]

@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.