Find the Largest Improvement Over Personal Best

[justafish2002]

My Data:

Name	May	June	July	Aug	Sept
Bob	100	50	150	175	200
Paul	50	100	175	250	200
Mary	25	20	150	100	50

<tbody>
</tbody>

I want to find the largest improvement over that person's personal best, so my desired results would be:

Name	Improvement	Month	Month
Mary	125	May	July

<tbody>
</tbody>

Some notes: I'd prefer no helper columns if possible. I'd also prefer to do this in a formula since I have to use Google Sheets.

I tried this formula, which works to find the max improvement on an individual row, but not on the data as a whole (since MAX doesn't return an array):
=ArrayFormula(MAX({G4:G666;H4:H666;I4:I666;J4:J666}-{MAX(F4:F666);MAX(F4:G666);MAX(F4:H666);MAX(F4:I666)}))

That formula would also require updating each time a column was added. Something that may be helpful is a formula that can return an array containing the max improvement of each row.

 

[Dave Patton]

Please clarify what "largest improvement over personal best means".
Does the second example below address your requirement?

Excel 2010
	A	B	C	D	E	F	G	H
2	Name	Rank	Max >	May	June	July	Aug	Sept
3	Bob	3	100	100	50	150	175	200
4	Paul	1	200	50	100	175	250	200
5	Mary	2	125	25	20	150	100	50
6
7
8	Name	Rank	Max >	May	June	July	Aug	Sept
9	Bob	3	50	100	50	150	175	200
10	Paul	2	75	50	100	175	250	200
11	Mary	1	125	25	20	150	100	50
12
2b

Cell Formulas
Range	Formula
B3	=RANK(C3,$C$3:$C$5)
B9	=RANK(C9,$C$9:$C$11)
C3	=MAX(E3:P3)-D3
C9	=MAX(E9-D9,F9-MAX(D9:E9),G9-MAX(D9:F9),H9-MAX(D9:G9))

 

[Aladin Akyurek]

Maybe this...

Row\Col	A​	B​	C​	D​	E​	F​
1​	Name	May	June	July	Aug	Sept
2​	Bob	100	50	150	175	200
3​	Paul	50	100	175	250	200
4​	Mary	25	20	150	100	50
5​	Joe		70	70	90	75
6​	Damon
7​	Linda	80	80		95
8​
9​	Name	Improve	Month	Month
10​	Bob	100	May	Sept
11​	Paul	200	May	Aug
12​	Mary	125	May	July
13​	Joe	20	June	Aug
14​	Damon
15​	Linda	15	May	Aug

In B10 control+shift+enter, not just enter, and copy down:

=IF(COUNT(INDEX($B$2:$F$7,MATCH($A10,$A$2:$A$7,0),0))>1,MAX(IF($A$2:$A$7=$A10,$B$2:$F$7-INDEX($B$2:$F$7,MATCH($A10,$A$2:$A$7,0),MIN(IF($A$2:$A$7=$A10,IF(ISNUMBER($B$2:$F$7),COLUMN($B$2:$F$7)-COLUMN($B$2)+1)))))),"")

In C10 control+shift+enter, not just enter, and copy down:

=IF($B10="","",INDEX($B$1:$F$1,MATCH(TRUE,ISNUMBER(1/INDEX($B$2:$F$7,MATCH($A10,$A$2:$A$7,0),0)),0)))

In D10 control+shift+enter, not just enter, and copy down:

=IF($C10="","",INDEX($B$1:$F$1,MATCH(INDEX($B2:$F2,MATCH($C10,$B$1:$F$1,0))+$B10,$B2:$F2,0)))

 

[justafish2002]

So neither solution quite fits my desired result, let me clarify:

Name	Rank	Improvement	May	June	July	Aug	Sept
Bob	2	100	100	50	150	175	200
Paul	3	75	50	100	175	250	200
Mary	1	125	25	20	150	100	50

<tbody>
</tbody>

So the the key is that the order matters. I'm looking for the largest difference between a cell and the largest cell to the left of it in the same row.

 

[Dave Patton]

Did you look at the second example (rows 8 to 11) that I posted?

 

[justafish2002]

Sorry Dave, I meant to reply. Your second solution does find the correct improvement, but it would require adding to the formula each time a new month is added. This isn't a big deal if the formula that returns the column of data in column C (as an array) so I can perform the max/large function on this array.

 

[Marcelo Branco]

So neither solution quite fits my desired result, let me clarify:

Name	Rank	Improvement	May	June	July	Aug	Sept
Bob	2	100	100	50	150	175	200
Paul	3	75	50	100	175	250	200
Mary	1	125	25	20	150	100	50

<tbody>
</tbody>

So the the key is that the order matters. I'm looking for the largest difference between a cell and the largest cell to the left of it in the same row.

Considering the data sample above shouldn't the result for Bob (Improvement) be 50 instead of 100?
150 in July - 100 in May

M.

 

[justafish2002]

Considering the data sample above shouldn't the result for Bob (Improvement) be 50 instead of 100?
150 in July - 100 in May

M.

Yes it should, the edit button disappear by the time I realized I made a mistake.

 

[Marcelo Branco]

Yes it should, the edit button disappear by the time I realized I made a mistake.

Maybe this...

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Name​	Rank​	Improvement​	May​	June​	July​	Aug​	Sept​
2​	Bob​	2​	50​	100​	50​	150​	175​	200​
3​	Paul​	3​	75​	50​	100​	175​	250​	200​
4​	Mary​	1​	125​	25​	20​	150​	100​	50​

<tbody>
</tbody>

Array formula in C2 copied down
=MAX(E2:Z2-SUBTOTAL(4,OFFSET(D2,,,,COLUMN(D2:Y2)-COLUMN(D2)+1)))
Ctrl+Shift+Enter

Remark: i assumed your data in the future will go as far as column Z - you can increase this range if necessary, but note that the first range (E2:Z2) should be shifted (offset) by one column relative to the second range (D2:Y2)

M.