Results 1 to 8 of 8

Thread: Formula to find the highest number in a row.

  1. #1

    Smile Formula to find the highest number in a row.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am returning to Excel after a long absence and hope someone can help me with what i think is a simple problem

    I made an expense spreadsheet that adds up what i spend each day and what i spent at the end of the month

    I want to format a cell to tell me what "Category" say "Groceries" has the highest amount and what has the lowest.

    I was thinking that the =HLOOKUP function was the way to go, but can't seem to get it right..

    What's the deal ? am i at least in the right ballpark ? thanks for any help.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,462
    Articles
    0
    Excel Version
    Excel 2016
    Can you give us an idea of how your data is setup and some sample expected inputs/results?


  3. #3
    Thanks for replying NBVC, i took sometime off to make the page simpler..the data set up is a simple date, budget and spending outcome, with totals going both vertical and horizontal.

    Here is an upload that should explain it better...
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,462
    Articles
    0
    Excel Version
    Excel 2016
    To get the column Header with the largest amount try:

    =INDEX($C$2:$G$2,MAX(IF($C$3:$G$8=MAX($C$3:$G$8),COLUMN($C$3:$G$8)-MIN(COLUMN($C$3:$G$8))+1)))

    this formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You should see { } brackets appear around the formula if done correctly.

    It seems possible that the MAX amount might appear more than once though. If so, and you want to list all the column headers that have that max number, then change formula to:

    =IFERROR(INDEX($C$2:$G$2,SMALL(IF($C$3:$G$8=MAX($C$3:$G$8),COLUMN($C$3:$G$8)-MIN(COLUMN($C$3:$G$8))+1),ROWS($B$12:$B12))),"")

    again, confirmed with CTRL+SHIFT+ENTER instead of just ENTER. Then copy the formula down as far as you want, you should see either more column headers or blanks.

    To get the smallest, it would be similar formulas, replacing all the the MAX functions with MIN.


  5. #5
    Thanks ! i knew it..it was an array solution. Thanks again.

  6. #6
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    Which version/edition of Excel are you running? I wouldn't recommend you keeping data structured like that. It makes for a bad idea when trying to get information out of it. I've used Power Query to restructure your data in the attached file, on Sheet 2. From there, since the data was in a table, I used a PivotTable to summarize the data, and then put conditional formatting on it. This is a sortable and filterable summarized table (aka PivotTable). To get any meaningful data out of the structure you had will always need some fancy formulas like the above posted.
    Attached Files Attached Files
    Regards,
    Zack Barresse

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,462
    Articles
    0
    Excel Version
    Excel 2016
    LOL, I am a fancy formula type of guy, you know


  8. #8
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    LOL! I'd say "fancy" was an understatement. I'd say "genius", but I wouldn't want it to go to your head!
    Regards,
    Zack Barresse

Posting Permissions

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