15
Sep
2015
Excel Bask
This is for a colleague who’s visiting us here at the Free Market Institute…
He as data in Excel with Year in Column A, and then items of interest in B through M (or whatever).
Now, at the very bottom, after the last year, he wants to have a row that returns the *year* of the MAX value in each column.
So for example, if row 100 is the last Year of 2014, then row 101 would say, “Max in Year:” and then in Column B, it might say “1984” while in Column C, it might say “1977.” That means that if you look at Column B, the max value occurred in Year 1984, etc.
So, what’s the easiest way for him to get Excel to populate that bottom row?
You want the VLOOKUP function. First value is the MAX value, second value is the “table” to lookup. In your example it is column A and B. B will exist as the value being looked up. A will have the value to return…the year.
Thanks. I originally thought of that but he said it wouldn’t work for what he wanted, but I think in retrospect it was that he forgot about the table option within VLOOKUP…
Dave’s answer might work, but according to Microsoft it shouldn’t.
According to their support document (https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1) the lookup value (the MAX in this case) must be to the left of the data being looked up (the year).
The easiest workaround to this limitation that springs to mind is to duplicate column A at the right of the table, then you can use VLOOKUP() and refer to the date to the right of the max value
http://www.mrexcel.com/forum/excel-questions/357763-vlookup-return-max-value-multiple-hits.html
This one may be better:
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-using-vlookup-with-min-max-and-average/
My understanding is that VLookup will only go left-to-right, meaning that the column containing the year numbers would have to be to the right of the columns containing the items of interest.
If I were solving this, I’d use a combination of the MAX function and the INDEX+MATCH functions, which don’t care where the data resides. The formula in the last row would be something like:
=INDEX([Year Column], MATCH(MAX([Column of Interest]),[Column of Interest],0))
That should return you the correct year number.
There’s no guarantee the maximum is unique, for example if you had a whole column of 3’s the maximum would be 3 but not in any particular year.
True. I didn’t see Bob specify a “tiebreaker” (e.g. taking the highest year value in column A) in instances where there were multiples of the same max value (either that, or my reading comprehension sucks), so I omitted mention of it. But in such a situation where you’d want to use the highest year value as a tiebreaker, I’m definitely in agreement with Oztrian’s solution.
Try
{=MAX(IF(B2:B100=MAX(B2:B100),$A2:$A100))}
That is, type =MAX(IF(B2:B100=MAX(B2:B100),$A2:$A100)) in B101 and then instead of Enter, do Control-Shift-Enter
Then copy to other columns.
Yep, that’s how I would have done it. Quickest and easiest way IMHO.
For 10 items
– Labels in row 3
– Year of Max in row 14
=INDEX(($A$4:$A$13,B4:B13),MATCH(MAX(B4:B13),B4:B13,0),,1)
Just extend the selection handles to match number of line items.
This formula will accommodate any number of columns.
Tel is correct: If the Max appears more than once, Excel picks the first one.
So, the earliest year in which the Max appears, would show, even if the same number apears for a later year.
Use R.
https://www.rstudio.com/
You sure you aren’t one of those New Sealanders?
In this case, old LOOKUP function, not VLOOKUP, works better. But remember to add the $ sign to the third parameter, so that it doesn’t change the letter when copying the formula.
Paste this in cell B101:
=LOOKUP(MAX(B1:B100),B1:B100,$A1:$A100)
Then you can populate to the right until column M or whatever. I prefer the copy, select and paste method, rather than the slection handle method, but both will work.
Thanks everybody.
You can also use a big-old-honking combination of the MATCH and INDIRECT functions, but the only real benefit there is improving your future Excel skills.
I think it’s fantastic that one excel question has 17 responding comments.
Murphy deserves it.
A little late to the party:
(1) bottom totaling is VERY poor form; top total where:
(a) you can see the answer
(b) the formulae are not dependent on the location of the last row of data
(c) the data users does not have to find/adjust for a different bottom row
(d) you can work with formulae across columns.
(2) the query is rather simple, i.e. two data points, one lookup. My answer comes closest to guest:
=INDEX($A5:$A65000,MATCH(MAX(B5:B65000),B5:B65000,0))
With top totaling, as an example, row 3: the answer, row 4: blank, row 5: headers, row 6: first row of data (grounding the formula in the header row will avoid the Reinhart Rogroff error).
The best thing about your answer is the nested labeling of your points.