+ Reply to Thread
Results 1 to 6 of 6

Finding multiple Min/max/Median of range given certain criteria

  1. #1
    Registered User
    Join Date
    08-05-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    60

    Finding multiple Min/max/Median of range given certain criteria

    I am looking for functions that will look at a given row and return the max, min, and median of a range based on the criteria from the row in focus. For example, if there is a location, product, and code, I want the function to refer to a dataset with a whole list of competitors, look for the competitors that match that location, product, and code, and return the minimum price, maximum price, and median for that category. I have some functions that seem to be right, but then they end up giving me weird numbers as I fill down further. Also, the min returns nothing but zeros even though I filtered zeros out in the dataset I have the function referring to.

    Example workbook attached.. Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Finding multiple Min/max/Median of range given certain criteria

    Hi,

    Your formula in H3 appears to be referencing worksheets (Competitors Ex Cost Blanks, Joe Vision) that have been removed from the workbook.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    08-05-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    60

    Re: Finding multiple Min/max/Median of range given certain criteria

    Here is a modified version.
    Attached Files Attached Files

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Finding multiple Min/max/Median of range given certain criteria

    "...look for the competitors that match that location, product, and code..."

    Your current formulas are attempting to match based on Location, Product Type, and Code.

    Please clarify (there are both Product Type and Product columns in your data).

    Regards

  5. #5
    Registered User
    Join Date
    08-05-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    60

    Re: Finding multiple Min/max/Median of range given certain criteria

    I said product, which refers to the product column.

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Finding multiple Min/max/Median of range given certain criteria

    Yes, but I'm sure that you can appreciate that it's a little confusing if your current attempts at a formula solution in the sheet use Product Type, can't you? Can I ask why that is?

    I'm only trying to get the correct information in order to be able to help you with this, after all.

    Regards

+ 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. YTD Median using Multiple Criteria
    By Decatur83 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-07-2013, 09:00 AM
  2. [SOLVED] Finding median of range of cells containing text and numbers
    By _hs_ in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-18-2013, 12:23 PM
  3. finding median with criteria
    By mheinmiller in forum Excel General
    Replies: 5
    Last Post: 12-18-2008, 05:54 PM
  4. finding he median subject to certain criteria
    By Cubsfan1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-20-2007, 04:14 PM
  5. Finding the median of numbers meeting criteria
    By thekovinc in forum Excel General
    Replies: 3
    Last Post: 02-06-2006, 08:45 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