Hi, I,m trying to write a formula to help speed up results at motorsport events we run. Column A- would have the car numbers, B- the drivers 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 score is input into column (D) when it does its second run its score is input into column (E) third run column (F) then a formula picks the highest of that cars three scores and automatically inputs it into column (G) I 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 score from column (G) moves to the top of the list. So if car number 5 had the highest score. he would automatically move to the top of the list, if car 7 had the second highest, he would move to second on the list and so on. Is what I'm trying to do possible? Thanks in advance for any help recieved,
Regards,
Kieran Hynes.

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)),Sheet 1!$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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"kiki" wrote in message
...

Hi, I,m trying to write a formula to help speed up results at motorsport
events we run. Column A- would have the car numbers, B- the drivers
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 score
is input into column (D) when it does its second run its score is input
into column (E) third run column (F) then a formula picks the highest of
that cars three scores and automatically inputs it into column (G) I
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 score
from column (G) moves to the top of the list. So if car number 5 had the
highest score. he would automatically move to the top of the list, if
car 7 had the second highest, he would move to second on the list and
so on. Is what I'm trying to do possible? Thanks in advance for any
help recieved,
Regards,
Kieran Hynes.


--
kiki

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"kiki" wrote in message
...

Hi, I,m trying to write a formula to help speed up results at motorsport
events we run. Column A- would have the car numbers, B- the drivers
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 score
is input into column (D) when it does its second run its score is input
into column (E) third run column (F) then a formula picks the highest of
that cars three scores and automatically inputs it into column (G) I
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 score
from column (G) moves to the top of the list. So if car number 5 had the
highest score. he would automatically move to the top of the list, if
car 7 had the second highest, he would move to second on the list and
so on. Is what I'm trying to do possible? Thanks in advance for any
help recieved,
Regards,
Kieran Hynes.


--
kiki

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"kiki" wrote in message
...

Hi, I,m trying to write a formula to help speed up results at motorsport
events we run. Column A- would have the car numbers, B- the drivers
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 score
is input into column (D) when it does its second run its score is input
into column (E) third run column (F) then a formula picks the highest of
that cars three scores and automatically inputs it into column (G) I
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 score
from column (G) moves to the top of the list. So if car number 5 had the
highest score. he would automatically move to the top of the list, if
car 7 had the second highest, he would move to second on the list and
so on. Is what I'm trying to do possible? Thanks in advance for any
help recieved,
Regards,
Kieran Hynes.


--
kiki

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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thanks Max and Ragdyer for your replies, Max I've downloaded the link you provided and had a bit of a play and it works so good I'm now going to see if I can get it to automatically fill in the sheets for the knockout rounds which I can print off and give to the marshal for lining up the cars. I'll let you know how I get on,
Regards,
Kieran

I've another question, I've made up another sheet which shows the 16 qualifiers, these are listed down column B, There is 8 heats, in the first heat, the highest qualifier goes head to head against the lowest qualifier in a knockout, after two runs the driver with the highest score goes through to the final 8. I've put in columns for each run and I've totaled them across so their total scores are shown, what I want to do is put a box in the next page where the winner of heat 1's car number (column B) is shown. Am I making sense? Regards Kieran.

=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 ...
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

You're welcome, Kieran !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"kiki" wrote in message
...

Thanks Max and Ragdyer for your replies, Max I've downloaded the link
you provided and had a bit of a play and it works so good I'm now going
to see if I can get it to automatically fill in the sheets for the
knockout rounds which I can print off and give to the marshal for
lining up the cars. I'll let you know how I get on,
Regards,
Kieran

Think I lost my way somewhere. But perhaps you would like to put in your new
question as a new post, together with sample data and expected results
pasted in plain text in the message area (Do not post any attachments). Or,
post a link together with your query for those interested to download your
sample file. All the best !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--