+ Reply to Thread
Results 1 to 8 of 8

Max value in table IF variable condition met

  1. #1
    Registered User
    Join Date
    07-16-2012
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    4

    Max value in table IF variable condition met

    I’ve got a table (example s/s attached), with:

    Date in column A (table won’t necessarily be sorted by date, but could be if needed)
    Person Name in column B
    Sales in column C
    I want to identify the largest Sales for each date (column D), so I can calculate each person's daily % of the largest daily sales (column E).

    I’ve searched the web, and tried a wide range of functions, including array functions, but can’t find something that seems to work for my situation. It seems this has to be something others have encountered and solved - it’s not that unique.

    Any suggestions would be greatly appreciated.
    - Frank
    Attached Files Attached Files

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Max value in table IF variable condition met

    welcome to the forum try

    =MAX(IF($A$6:$A$26=A6,$C$6:$C$26)) ctrl+shift+enter
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Max value in table IF variable condition met

    Hi Frank and welcome to the forum,

    Pivot Tables has this Max by Date capability. See attached.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    07-16-2012
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Max value in table IF variable condition met

    The problem with:
    =MAX(IF($A$6:$A$26=A6,$C$6:$C$26))

    at least as far as I can see, is that it uses a fixed value for A6, so all rows have same result (70), which is not what I was looking for.

    If anyone know of a way to use this technique, but vary the value used for the comparison so it achieves the result I’m looking for, please let me know.




    Quote Originally Posted by vlady View Post
    welcome to the forum try

    =MAX(IF($A$6:$A$26=A6,$C$6:$C$26)) ctrl+shift+enter

  5. #5
    Registered User
    Join Date
    07-16-2012
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Max value in table IF variable condition met

    Very nice!

    Using the Pivot table report as a source, I can create a LOOKUP function in my original table to pull the pivot Max-Of-Sales into the Highest-Sales-Of-The-Day field in my original table.

    I’ll leave this thread open for another day or so to see if there’s any way to solve this with a self-contained formula (without resorting to an external table), but if not, your solution is an excellent one.

    Thanks so much for taking the time post this. I knew there had to be way ...
    - Frank


    Quote Originally Posted by MarvinP View Post
    Hi Frank and welcome to the forum,

    Pivot Tables has this Max by Date capability. See attached.

  6. #6
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Max value in table IF variable condition met


  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Max value in table IF variable condition met

    Hey Frank and Vlady,

    @vlady - you made me work on this problem a little more. See the Pivot Table in this attached. I think this is what Frank wanted all along. No formulas needed. Simply used Pivot Table options.
    Attached Files Attached Files
    Last edited by MarvinP; 07-17-2012 at 11:26 PM.

  8. #8
    Registered User
    Join Date
    07-16-2012
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Max value in table IF variable condition met

    vlady,

    Thanks! I think I misunderstood your original solution - I had used your same array formula in all cells in column D.

    Looking at your additional spreadsheet, I now see that you define an array formula in just one cell (I had never done that before). I didn't follow all that you did, but this one technique is exactly what I needed.

    I've posted a copy of the solution, using just a formula in column D, no outside fields needed.

    This resolves my problem. Many thanks to both responders, this is such a helpful forum.
    - Frank
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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