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

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:
`Please Login or Register  to view this content.`

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

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.

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

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!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1