Formula to find the highest number in a row.

cousindupree

New member
Joined
Jun 16, 2014
Messages
3
Reaction score
0
Points
0
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.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Can you give us an idea of how your data is setup and some sample expected inputs/results?
 

cousindupree

New member
Joined
Jun 16, 2014
Messages
3
Reaction score
0
Points
0
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...
 

Attachments

  • smaller_expense.xlsx
    15.9 KB · Views: 21

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

Zack Barresse

Super Moderator
Staff member
Joined
Mar 16, 2011
Messages
112
Reaction score
0
Points
0
Location
Oregon, United States
Excel Version(s)
365, Online, iOS, iOS for iPad
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.
 

Attachments

  • smaller_expense.xlsx
    24.7 KB · Views: 11

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
LOL, I am a fancy formula type of guy, you know :peace:
 

Zack Barresse

Super Moderator
Staff member
Joined
Mar 16, 2011
Messages
112
Reaction score
0
Points
0
Location
Oregon, United States
Excel Version(s)
365, Online, iOS, iOS for iPad
LOL! I'd say "fancy" was an understatement. I'd say "genius", but I wouldn't want it to go to your head!
 
Top