Average values in a column dependent of even and odd label values plus ignoring 0's

1. Average values in a column dependent of even and odd label values plus ignoring 0's

Hi Everyone,

Please see the attached image of the spreadsheet I am working on, hopefully this will make sense.

I'm trying to work out the formula needed to take the values in each column ranging from B - D dependent on whether its corresponding route number in column A is odd or even and then calculate the average (mean) value of that column while ignoring any cells with a 0 value.  Register To Reply

2. Re: Average values in a column dependent of even and odd label values plus ignoring 0's

Hi, Welcome to the forum, Try this

Formula:  You can post sample of the excel file, rather than a pic, it will help us to examine...  Register To Reply

3. Re: Average values in a column dependent of even and odd label values plus ignoring 0's

Do you want the average of each individual column or the combined columns?

Here's one way for a single column.

Data Range
 A B C D E 1 Route Value ------ Odd Even 2 155 86 43 19.33333 3 160 3 4 218 0 5 239 19 6 260 35 7 267 0 8 298 0 9 382 20 10 493 24

These array formulas** in D2 & E2:

=AVERAGE(IF(ISODD(A2:A10+0),IF(B2:B10>0,B2:B10)))

=AVERAGE(IF(ISEVEN(A2:A10+0),IF(B2:B10>0,B2:B10)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.  Register To Reply

4. Re: Average values in a column dependent of even and odd label values plus ignoring 0's

Thank you both for your help. Its really appreciated  Register To Reply

5. Re: Average values in a column dependent of even and odd label values plus ignoring 0's

You're welcome. Thanks for the feedback!   Register To Reply