Formula help

[jays35]

I need to take scores from 6 candidates that their scores or totaled in a row. I need to find the 'points less than the highest score' in that array and also I need to find a 'percentage less than the highest score'. I have attached a clip of the sheet that I am working on a thanks in advance for the help.

Dave

[TABLE="width: 752"]
<tbody>[TR]
[TD]total points[/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD][/TD]
[TD]total score[/TD]
[TD]7[/TD]
[TD]7.5[/TD]
[TD]8.7[/TD]
[TD]7.1[/TD]
[TD]5.2[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD="colspan: 5"]POINTS LESS THAN HIGHEST[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 5"]% LESS THAN HIGH SCORE[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col span="2"><col><col span="2"><col></colgroup>[/TABLE]

 

[Special-K99]

So what would your results be given that data?
Surely not all zeroes?

 

[SpillerBD]

Pasting that table to Excel....
For Cell F2

=MAX($F$1:$K$1)-F1

fill right

For Cell F3

=F2/MAX(F1:$K$1)

 

[Dave Patton]

You can edit and expand this to address your challenge.
It would be much better to post data that we can use in Excel.

Excel 2010
	A	B	C	D	E	F	G	H	I
2	Scores			7	7.5	8.7	7.1	5.2	7.5
3	Max	8.7
4	Total	43	% less Max	19.5%	13.8%	0.0%	18.4%	40.2%	13.8%
5	Total - Max	34.3
2e

Cell Formulas
Range	Formula
D4	=($B$3-D2)/$B$3
E4	=($B$3-E2)/$B$3
F4	=($B$3-F2)/$B$3
G4	=($B$3-G2)/$B$3
H4	=($B$3-H2)/$B$3
I4	=($B$3-I2)/$B$3
B3	=MAX(D2:I2)
B4	=SUM(D2:I2)
B5	=B4-B3

 

[jays35]

That is where I need the help as to the formula that would be in those cells. The total scores are the top rows and the formulas would reside where the 0 and 0.0% are.

 

[jays35]

Thanks and sorry as I a new to this board.

Dave

 

[jays35]

Thanks!