Combined MAX and IF statements?

[ddansie]

I am using Excel 2013.
I have two sheets, 1st with the raw data, 2nd for a summary page.
Raw Data Page:
Column A List of locations
Column B Numbers


In Row 1 column B I have a MAX statement which gives me my MAX number.

Summary Page:
I want to be able to list the location that has the MAX number with the number next to it.

Any help would be greatly appreciated.

-Dale

[iclendening]

I admit I started thinking about the problem just as you phrased it, 'how do I get the corresponding location value to my maximum number value'; however, this is best managed by a pivot table.

1. (optional) select data on sheet 1 and turn it into a table
2. create a pivot table
3. organize with list of locations in the 'rows' and numbers in the 'values'; ensure that values are set to show maximum (as opposed to sum etc).
4. right click on one of your maximum values shown for a given location and select 'organize by' and choose 'highest to lowest'. Your highest value is now at the top, and immediately left of it is the corresponding location.

hope that helps - the vlookup would not have been transparent to you about duplicates, this way you know if there is a tie for the winner.

ian.

[Pecoflyer]

Posting a small sample sheet of your data would help

[ddansie]

Here is a small example, not the real data. There are over 400 rows of data in the real one.

[Pecoflyer]

The location for the highest nr of " beds" can be obtained by =INDEX('RAW DATA'!$A$6:$A$12;MATCH(MAX('RAW DATA'!B$6:B$12);'RAW DATA'!B$6:B$12;0))
pull right for other values
For the lowest, the same, using MIN instead of MAX

This will work as long as the three columns stay in the same order on both pages.
If not, some tweaking is necessary but easy to do.

OTH what should happen if there is more than one MAX or MIN?

[ddansie]

I'm not sure what you mean when you asked: "what should happen if there is more than one MAX or MIN?" Do you mean if there are two values that are the same?

[ddansie]

This formula works, but the ";" needs to be replaced with ","

Thank you for your help.

[Pecoflyer]

Oops, my mistake
And yes, what happens when you have a tie ( is that the correct word?)

[ddansie]

With what I am doing the chances of that ever happening is slim to none (not saying that it would never happen), not sure what I will do at that point.