Simple formula help please...

[Sysyphus]

This should be simple...but it's beyond my knowledge.

I have column A (20 Drivers) Column B (position in race 1-20)

I want column C to show points won based on the cell contents in adjacent
cells in column B.
Where first place (cell entry=1) gives 10 points, second (2) gives 8, third
gives 6 down to 1pt for eigth place.
9th and below should give zero points.

What formula should I be using in column C?

Thanks for any help.

 

[Sandy Mann]

Try:
=MAX((B1<4)*(12-B1*2)+(B1>4)*(10-B1),0)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Mladen_Dj]

Sandy, your formula was not return correct result for 4th place to me.
The folowing one gives right points:

=IF(RANK(B1;$B$1:$B$20;1)>=9;0;CHOOSE(RANK(B1;$B$1:$B$20;1);
10;8;6;5;4;3;2;1))

Is this for F1 standings Sysyphus? ;-) Enter it in C1 and copy to C20
cell. Depending on your local settings maybe you will need to change
all coma-points to comas.

 

[kounoike]

I think Sandy meant something like this

=MAX((B1>0)*(B1<3)*(12-B1*2)+(B1>=3)*(9-B1),0)

and copy it down to C20.

keizi

 

[Sandy Mann]

Yes, that's because I tested one formula and posted another, (don't ask me
how I did it!)

What I should have posted was:

=MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0)

Which returns the same results as your when I substitute , for ;

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

Not quite but your does work. What I meant was:

=MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0)

--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sysyphus]

Yes, that's because I tested one formula and posted another, (don't ask me
how I did it!)

What I should have posted was:

=MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0)

Which returns the same results as your when I substitute , for ;

<OP>

Thanks all...
Sandy, this works beautifully. Thank you.

Unfortunately, I forgot to mention(!) I also a few drivers with 'position 0'
(user entry) for DNF's (Did not finish race) in column B.
This formula gives all zero entries 12 points - Can I append/insert an =IF
function into your equation?
ie. IF(B1=0, 0) or something?

 

[Sysyphus]

Sandy, your formula was not return correct result for 4th place to me.
The folowing one gives right points:

=IF(RANK(B1;$B$1:$B$20;1)>=9;0;CHOOSE(RANK(B1;$B$1:$B$20;1);
10;8;6;5;4;3;2;1))

Is this for F1 standings Sysyphus? ;-)

Is it *that* transparent?
I suppose I should have just said so.... :+)

 

[Sandy Mann]

Yes certainly, Your solution will work very well:

=IF(B1=0,0,MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0))

Or if you want to use DNF in place to the zero then:

=IF(B1="DNF",0,MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0))

Or if you want to use either:

=IF(OR(B1="DNF",B1=0),0,MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0))

Or even if you want to enter various different text messages:

=IF(ISTEXT(B1),0,MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sysyphus]

Not quite but your does work. What I meant was:

=MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0)

Many thanks to all for the help.
I'd never have figured this complex (to me) formuala.

Cheers.

 

[Sandy Mann]

You're welcome, sorry I messit it up to start with.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk