+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    11-07-2014
    Location
    Peterborough England
    MS-Off Ver
    Microsoft Office 2010
    Posts
    2

    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.

    Spreadsheet 1.jpg

    Thank you in advance for any help you can give

  2. #2
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    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: copy to clipboard
    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...
    Click just below left if it helps, Boo?ath?

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    11-07-2014
    Location
    Peterborough England
    MS-Off Ver
    Microsoft Office 2010
    Posts
    2

    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
    Last edited by cstrangward; 11-19-2014 at 06:15 PM.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculating rolling average ignoring 0 values
    By Whitz in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-07-2024, 05:51 PM
  2. Average Ignoring Zero Values - Non-consecutive cells
    By a7mad3wies in forum Excel General
    Replies: 2
    Last Post: 12-05-2013, 02:13 AM
  3. [SOLVED] Excel 2010 Average Ignoring Zero Values - Non-consecutive cells
    By lanos in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 10-23-2013, 07:28 PM
  4. Replies: 1
    Last Post: 09-30-2013, 09:52 AM
  5. Conditional weighted average ignoring #N/A values
    By syoung27 in forum Excel General
    Replies: 4
    Last Post: 02-22-2012, 10:48 AM

Bookmarks

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