+ Reply to Thread
Results 1 to 2 of 2

Trying to find median & average for multiple columns with multiple criteria

  1. #1
    Registered User
    Join Date
    02-11-2016
    Location
    usa number 1
    MS-Off Ver
    2013
    Posts
    3

    Trying to find median & average for multiple columns with multiple criteria

    Hey friends,

    I am attempting to get the average and median, but i just cant write a winning formula. attached is the sample/dummy data and the spot where i need help is in the green transaction fundamentals table.

    The blue table above it is fine as is....for clarity, the blue table pulls all data from the right into it and in theory the green one is supposed to pull only the complete data (meaning data that has calculations in ever single "named title" column)

    cells C52:J53 are where i need help

    please help ease my jumbled brain

    i can try to clear it up more if i'm not making the most sense
    Book33.xlsx

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Trying to find median & average for multiple columns with multiple criteria

    Might I make a suggestion:

    =MEDIAN((IF(ISNUMBER(Z5:Z600),IF(ISNUMBER(N5:N600),IF(ISNUMBER(T5:T600),IF(ISNUMBER(AF5:AF600),IF(ISNUMBER(AL5:AL600),IF(ISNUMBER(AR5:AR600),IF(ISNUMBER(AX5:AX600),IF(ISNUMBER(BD5:BD600),N5:N600))))))))))

    How about a helper column to merge this into a single criteria? = AND(ISNUMBER(Z5),ISNUMBER(N5),ISNUMBER(T5),ISNUMBER(AF5),ISNUMBER(AR5),ISNUMBER(AX5),ISNUMBER(DB5))

    Maybe you can express that in an array formula.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ 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. Trying to find median & average for multiple columns with multiple criteria
    By help-meplease in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-11-2016, 06:59 PM
  2. Average with Multiple Criteria and on Different Columns
    By noskcire24 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 10-12-2015, 09:01 PM
  3. Replies: 0
    Last Post: 03-01-2015, 11:34 PM
  4. Average numbers from multiple columns based on multiple criteria
    By abambi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-23-2015, 01:52 PM
  5. [SOLVED] If any combinations of multiple criteria met, then find average time elapsed
    By Verdant in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2013, 01:57 AM
  6. Average GP from multiple columns with criteria
    By t+ccyco in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-30-2013, 07:06 PM
  7. [SOLVED] Average Based on Criteria in Multiple Columns
    By sinspawn56 in forum Excel General
    Replies: 2
    Last Post: 07-31-2012, 01:27 PM

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