Find the Largest Improvement Over Personal Best

[justafish2002]

My Data:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]Aug[/TD]
[TD]Sept[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]150[/TD]
[TD]175[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]175[/TD]
[TD]250[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]150[/TD]
[TD]100[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]


I want to find the largest improvement over that person's personal best, so my desired results would be:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Improvement[/TD]
[TD]Month[/TD]
[TD]Month[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]125[/TD]
[TD]May[/TD]
[TD]July[/TD]
[/TR]
</tbody>[/TABLE]


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...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

1​

[/td][td] Name[/td][td] May[/td][td] June[/td][td] July[/td][td] Aug[/td][td] Sept[/td][/tr]
[tr][td]

2​

[/td][td] Bob[/td][td] 100[/td][td] 50[/td][td] 150[/td][td] 175[/td][td] 200[/td][/tr]
[tr][td]

3​

[/td][td] Paul[/td][td] 50[/td][td] 100[/td][td] 175[/td][td] 250[/td][td] 200[/td][/tr]
[tr][td]

4​

[/td][td] Mary[/td][td] 25[/td][td] 20[/td][td] 150[/td][td] 100[/td][td] 50[/td][/tr]
[tr][td]

5​

[/td][td] Joe[/td][td][/td][td] 70[/td][td] 70[/td][td] 90[/td][td] 75[/td][/tr]
[tr][td]

6​

[/td][td] Damon[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td] Linda[/td][td] 80[/td][td] 80[/td][td][/td][td] 95[/td][td][/td][/tr]
[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td] Name[/td][td] Improve[/td][td] Month[/td][td] Month[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td] Bob[/td][td] 100[/td][td] May[/td][td] Sept[/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td] Paul[/td][td] 200[/td][td] May[/td][td] Aug[/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td] Mary[/td][td] 125[/td][td] May[/td][td] July[/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td] Joe[/td][td] 20[/td][td] June[/td][td] Aug[/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td] Damon[/td][td] [/td][td] [/td][td] [/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td] Linda[/td][td] 15[/td][td] May[/td][td] Aug[/td][td][/td][td][/td][/tr]
[/table]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Rank[/TD]
[TD]Improvement[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]Aug[/TD]
[TD]Sept[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]2[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]150[/TD]
[TD]175[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]3[/TD]
[TD]75[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]175[/TD]
[TD]250[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]1[/TD]
[TD]125[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]150[/TD]
[TD]100[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Rank[/TD]
[TD]Improvement[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]Aug[/TD]
[TD]Sept[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]2[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]150[/TD]
[TD]175[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]3[/TD]
[TD]75[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]175[/TD]
[TD]250[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]1[/TD]
[TD]125[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]150[/TD]
[TD]100[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]


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...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Name​

[/TD]
[TD]

Rank​

[/TD]
[TD]

Improvement​

[/TD]
[TD]

May​

[/TD]
[TD]

June​

[/TD]
[TD]

July​

[/TD]
[TD]

Aug​

[/TD]
[TD]

Sept​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Bob​

[/TD]
[TD]

2​

[/TD]
[TD]

50​

[/TD]
[TD]

100​

[/TD]
[TD]

50​

[/TD]
[TD]

150​

[/TD]
[TD]

175​

[/TD]
[TD]

200​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Paul​

[/TD]
[TD]

3​

[/TD]
[TD]

75​

[/TD]
[TD]

50​

[/TD]
[TD]

100​

[/TD]
[TD]

175​

[/TD]
[TD]

250​

[/TD]
[TD]

200​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Mary​

[/TD]
[TD]

1​

[/TD]
[TD]

125​

[/TD]
[TD]

25​

[/TD]
[TD]

20​

[/TD]
[TD]

150​

[/TD]
[TD]

100​

[/TD]
[TD]

50​

[/TD]
[/TR]
</tbody>[/TABLE]


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.