Formula Help

[kiki]

Hi, I,m trying to write a formula to help speed up results at motorspor
events we run. Column A- would have the car numbers, B- the driver
name, C-make of the car, D- score for run one, E- score for run two,
F- for run three, and G- Highest score from D-E-F.
What I'm trying to achive is, as each car does its first run its scor
is input into column (D) when it does its second run its score is inpu
into column (E) third run column (F) then a formula picks the highest o
that cars three scores and automatically inputs it into column (G)
need to find a way to lock the horizontal column so the car number
Driver, car make and its scores all stay together, and the higest scor
from column (G) moves to the top of the list. So if car number 5 had th
highest score. he would automatically move to the top of the list, i
car 7 had the second highest, he would move to second on the list an
so on. Is what I'm trying to do possible? Thanks in advance for an
help recieved,
Regards,
Kieran Hynes

 

[Max]

One play, using non-array formulas ..
(Link to a sample file is provided below)

In Sheet1
----
The source table is assumed in cols A to F,
headers in row1, data from row2 down

Put in G1: =IF(COUNTBLANK(C2:E2)=0,MAX(C2:E2),"")
Put in H1: =IF(G2="","",G2-ROW()/10^10)
(Leave G1 & H1 empty)

Select G1:H1, fill down to say, H20
to cover the max expected data in cols A to F

Col G computes the max score for each car
once the scores for runs 1 to 3 are entered

Col H functions as an arbitrary tie-breaker,
to cater for cases of identical max scores (there may be ties !)

In Sheet2
----
Paste the same headers into A1:F1,
add a new header in G1: HighestRun

Put in A2:
=IF(ISERROR(LARGE(Sheet1!$H:$H,ROWS(Sheet1!$A$1:A1))),"",INDEX(Sheet1!A:A,MA
TCH(LARGE(Sheet1!$H:$H,ROWS(Sheet1!$A$1:A1)),Sheet1!$H:$H,0)))

Copy A2 across to G2, fill down to G20
(cover the same range as done in cols G & H in Sheet1)

Sheet2 will return a full dynamic descending sort of the data lines in
Sheet1, with all results neatly bunched at the top

And in the event of any ties in the max scores, data lines for the car#s,
etc will be returned in the same relative order that they appear in Sheet1.
For example, if cars #1 and #5 have identical max scores, and car#1's row is
above car#5's in Sheet1, then car#1 will appear above car#5 in the results

Here's a sample file with the implemented construct:
http://www.savefile.com/files/4445426
File: Motorsports_Formula_kiki_newusers.xls

 

[Ragdyer]

Enter this formula in G1:

=MAX(D1:F1)

Then copy down as needed.

Now, select your *entire* data list.
Click in A1, and hold <Shift>, and click in the last row of Column G that
contains you Max() formula.
While everything is *still* selected, click in the "name box" (left of the
formula window), and type in a short name, such as "sort1" (no quotes), and
then hit <Enter>.

You have just created a named range.

NOW, after all the scores are entered, and the Max() formula has returned
the highest score of each car, you can Sort the entire data list.

Click in the name box, and click on the range name "sort1".

Your *entire* data list will be selected.
Now, <Data> <Sort>,
click on Column G,
click on descending, then <OK>,
And you're done.

 

[Max]

Scratch the earlier post (some typos & errors),
here's a corrected re-post ..

One play, using non-array formulas ..
(Link to a sample file is provided below)

In Sheet1
----
The source table is assumed in cols A to F,
headers in row1, data from row2 down

Put in G2: =IF(COUNTBLANK(D2:F2)=0,MAX(D2:F2),"")
Put in H2: =IF(G2="","",G2-ROW()/10^10)
(Leave G1 & H1 empty)

Select G2:H2, fill down to say, H20
to cover the max expected data in cols A to F

Col G computes the max score for each car
once the scores for runs 1 to 3 are entered

Col H functions as an arbitrary tie-breaker,
to cater for cases of identical max scores (there may be ties !)

In Sheet2
----
Paste the same headers into A1:F1,
add a new header in G1: HighestRun

Put in A2:
=IF(ISERROR(LARGE(Sheet1!$H:$H,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(LAR
GE(Sheet1!$H:$H,ROWS($A$1:A1)),Sheet1!$H:$H,0)))

Copy A2 across to G2, fill down to G20
(cover the same range as done in cols G & H in Sheet1)

Sheet2 will return a full dynamic descending sort of the data lines in
Sheet1, with all results neatly bunched at the top

And in the event of any ties in the max scores, data lines for the car#s,
etc will be returned in the same relative order that they appear in Sheet1.
For example, if cars #1 and #5 have identical max scores, and car#1's row is
above car#5's in Sheet1, then car#1 will appear above car#5 in the results

Here's a sample file with the implemented construct:
http://www.savefile.com/files/4445426
File: Motorsports_Formula_kiki_newusers.xls

 

[Max]

In Sheet1

Put in G2: =IF(COUNTBLANK(D2:F2)=0,MAX(D2:F2),"")

And if you want Sheet2 to display the results progressively ("live") as the
scores are input from the very first run onwards for the cars, instead of
waiting only until all 3 runs' scores are input (your original specs), just
change the formula in the starting cell Sheet1's G2 to:

=IF(COUNTBLANK(D2:F2)<3,MAX(D2:F2),"")

(The rest of the set-up steps remain unchanged)

 

[Max]

=IF(COUNTBLANK(D2:F2)<3,MAX(D2:F2),"")

Note that there is a "less than" operator symbol just before the "3" in the
revised formula above. From where you're reading this (Excelbanter.com),
unfortunately such operator symbols [ less than, greater than ] won't appear
at all. So you've to be aware of this and insert the missing symbol(s)
manually ...