Difference from Top Rated Value in a Range

[paulfitz320]

Book3
	A	B
1	Rating	Wanted
2	89	-2
3	87	2
4	87	2
5	86	3
6	81	8
7	76	13
8	75	14
9	73	16
10	66	23
11	61	28
Sheet1

Hi,
Would appreciate a formula for the 'Wanted' column please.
It needs to return the difference between the Top Rated and Adjacent Value in the 'Rating' column.
Also the value calculated for the Highest Rated should be the difference between the Highest Rated and Second Highest Rated then converted to a minus value. Here 89 is 2 more than the next highest value in the range 87 so gets a -2
Thanks.

 

[Asbestos_Jen]

Try this:

2024-02-29.xlsx
	A	B	C
1	Rating	Wanted	Formula
2	89	-2	-2
3	87	2	2
4	87	2	2
5	86	3	3
6	81	8	8
7	76	13	13
8	75	14	14
9	73	16	16
10	66	23	23
11	61	28	28
Sheet2

Cell Formulas
Range	Formula
C2:C11	C2	=IF(A2=LARGE($A$2:$A$11,1),LARGE($A$2:$A$11,2)-A2,LARGE($A$2:$A$11,1)-A2)

 

[dreid1011]

Perhaps this:

Book1
	A	B	C
1	Rating	Wanted	Formula
2	89	-2	-2
3	87	2	2
4	87	2	2
5	86	3	3
6	81	8	8
7	76	13	13
8	75	14	14
9	73	16	16
10	66	23	23
11	61	28	28
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=LET(m,MAX($A$2:$A$11),IF(A2=m,LARGE($A$2:$A$11,2)-A2,m-A2))

 

[paulfitz320]

Thanks, they both worked fine.
One more thing...would it be possible to modify the formula to accommodate joint top ratings - lets say 89 was there twice, it would return -2 twice in the range. Having tried this with the solutions provided, its returning 0 for both top rated in the wanted column.
Thanks again.

 

[dreid1011]

Try this formula in column D:

Book1
	A	B	C	D
1	Rating	Wanted	Formula	New Formula
2	89	-2	0	-2
3	89	2	0	-2
4	87	2	2	2
5	86	3	3	3
6	81	8	8	8
7	76	13	13	13
8	75	14	14	14
9	73	16	16	16
10	66	23	23	23
11	61	28	28	28
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=LET(m,MAX($A$2:$A$11),IF(A2=m,LARGE($A$2:$A$11,2)-A2,m-A2))
D2:D11	D2	=IF(A2=MAX($A$2:$A$11),LARGE(UNIQUE($A$2:$A$11),2)-LARGE(UNIQUE($A$2:$A$11),1),MAX($A$2:$A$11)-A2)

Or slightly shorter:

=LET(m,MAX($A$2:$A$11),u,UNIQUE($A$2:$A$11),IF(A2=m,LARGE(u,2)-LARGE(u,1),m-A2))

 

[paulfitz320]

Perfect, dreid. Thanks, much appreciated!

 

[dreid1011]

Perfect, dreid. Thanks, much appreciated!

You're welcome, and happy to help. Though you may want to adjust which post you marked as the solution.

 

[paulfitz320]

Done. Thanks.

 

[paulfitz320]

Book7
	A	B	C	D	E
1	Group	Rating	Wanted	Formula	New Formula
2	Cat	89	-2	0	-2
3	Cat	89	2	0	-2
4	Cat	87	2	2	2
5	Cat	86	3	3	3
6	Cat	81	8	8	8
7	Cat	76	13	13	13
8	Cat	75	14	14	14
9	Cat	73	16	16	16
10	Cat	66	23	23	23
11	Cat	61	28	28	28
12	Dog	89	-2	0	-2
13	Dog	89	2	0	-2
14	Dog	87	2	2	2
15	Dog	86	3	3	3
16	Dog	81	8	8	8
17	Dog	76	13	13	13
18	Dog	75	14	14	14
19	Dog	73	16	16	16
20	Dog	66	23	23	23
21	Dog	61	28	28	28
Sheet1

Cell Formulas
Range	Formula
D2:D21	D2	=LET(m,MAX($B$2:$B$11),IF(B2=m,LARGE($B$2:$B$11,2)-B2,m-B2))
E2:E21	E2	=IF(B2=MAX($B$2:$B$11),LARGE(UNIQUE($B$2:$B$11),2)-LARGE(UNIQUE($B$2:$B$11),1),MAX($B$2:$B$11)-B2)

Hi there,
Just wondering would it be possible to edit the formula in COL E to take account of the different Groups in Col A. The groups in COL A extend further down the sheet, so the formula would need to dynamically adjust for this.
Thanks

 

[dreid1011]

Okay, see the formula in col F:

Book1
	A	B	C	D	E	F
1	Group	Rating	Wanted	Formula	New Formula	New New Formula
2	Cat	89	-2	0	6	-2
3	Cat	89	2	0	6	-2
4	Cat	87	2	2	8	2
5	Cat	86	3	3	9	3
6	Cat	81	8	8	14	8
7	Cat	76	13	13	19	13
8	Cat	75	14	14	20	14
9	Cat	73	16	16	22	16
10	Cat	66	23	23	29	23
11	Cat	61	28	28	34	28
12	Dog	95	-6	-6	-6	-6
13	Dog	89	6	0	6	6
14	Dog	87	8	2	8	8
15	Dog	86	9	3	9	9
16	Dog	81	14	8	14	14
17	Dog	76	19	13	19	19
18	Dog	75	20	14	20	20
19	Dog	73	22	16	22	22
20	Dog	66	29	23	29	29
21	Dog	49	46	40	46	46
Sheet2

Cell Formulas
Range	Formula
D2:D21	D2	=LET(m,MAX($B$2:$B$11),IF(B2=m,LARGE($B$2:$B$11,2)-B2,m-B2))
E2:E21	E2	=IF(B2=MAX($B$2:$B$21),LARGE(UNIQUE($B$2:$B$21),2)-LARGE(UNIQUE($B$2:$B$21),1),MAX($B$2:$B$21)-B2)
F2:F21	F2	=LET(f,TAKE(FILTER($A$2:$B$21,$A$2:$A$21=A2),,-1),IF(B2=MAX(f),LARGE(UNIQUE(f),2)-LARGE(UNIQUE(f),1),MAX(f)-B2))