Calculating values need to beat top score

[Ducklings23]

Hi all. I'm working on a project that ranks proposals based on a score that is generated by a formula that is calculated using the price and points submitted by each team. The score is then ranked, the highest wins (see attached example). What I want to do now is calculate what value the other 4 teams would need to remove from their price/add to their points to beat the score of the team ranked first. Kind of like a goal seek, but I want a formula that will do it for each of the other teams and update if the values change without having to run individual goal seeks.Any ideas? Thanks.

 

[Eric W]

How is the score calculated? It shouldn't be hard to figure out if we know the formula.

 

[Ducklings23]

In this case its just the points divided by the score, multiplied by 1000 to give the score. Thanks

 

[Ducklings23]

Sorry - its the points divided by price, multiplied by 1000 to get the score

 

[Eric W]

Try this:

Book2
	A	B	C	D	E	F	G	H
1
2		Price	Points	Score	Rank		Price change needed	Point change needed
3	team 1	15689	23	1.47	1		0	0.00
4	team 2	35984	28	0.78	3		16884.35	24.75
5	team 3	68597	26	0.38	4		50861.61	74.56
6	team 4	23145	32	1.38	2		1316.826	1.93
7	team 5	96422	15	0.16	5		86190.04	126.35
Sheet3

Cell Formulas
Range	Formula
D3:D7	D3	=C3/B3*1000
E3:E7	E3	=RANK(D3,D$3:D$7)
G3:G7	G3	=B3-1000*C3/MAX(D$3:D$7)
H3:H7	H3	=MAX(D$3:D$7)/1000*B3-C3

 

[Ducklings23]

Ok thanks, that seems to work!