Race points formula

**shanshine** · 03-06-2013, 05:47 PM

I need some help editing this spreadsheet again.

The first formula works fabulous.

I just need two more.

The formula in the season total colum already takes away a racers WORST (or lowest points) races. I just need that amount of the WORST races to show up in the Dropped Races Column.

The other thing I need is a formula for the bonus point column.
Basically the way it works is like this...if you attend 5 races you get 25 points if you attend 6 you get an additional 50 and if you attend all 7 you get an additional 100 for a total of 175. Is there a way to write a formula that says if you get points in the "S" column under the races that it will count them and add that number in autmatically? So say for racer 1 he should have 75 points already in the bonus field and once I add in points for race 7 in should change to 175.
Does that make sense?

Here is the link to the original thread http://www.excelforum.com/excel-form...html?p=3095233

And I have attached a sample spreadsheet

**JBeaucaire** · 03-06-2013, 05:55 PM

Maybe this:

W3: =CHOOSE(SUMPRODUCT(--($B$2:$V$2="Q"), --($B3:$V3>0)),0,0,0,0,25,75,175)
X3: =SUM(B3:W3)-Y3

**shanshine** · 03-06-2013, 07:14 PM

Ok I missed something so let me try this again....the spreadsheet was kinda jacked up

As you can see only Racer 1 and 2 recieved bonus points so I need a formula that will compute the bonus points. They get 25 for attending 4 races an additional 50 for attending 5 races and an additional 100 for all 6 (for a total of 175) I need it to compute them "showing" up to the event by the column titled "S" under each individual race. This formula will go in the V column.

The next thing I need is a new formula for the best 4 out of 6 events.
This is the formula I had for the best 5 out of 7 and I cant figure out how to edit it without jacking it up

=SUM(LARGE(SUBTOTAL(9,OFFSET($D23:$F23,0,{0,3,6,9,12,15,18})),{1,2,3,4,5}),Y23)

This Formula will go in the W column

Can we try this again????

Thanks guys!!!

**daddylonglegs** · 03-06-2013, 07:31 PM

Try this formula for V3

=LOOKUP(COUNTIF(D3:U3,">0"),{0,12,15,18},{0,25,75,175})

and this one for W3

=SUM(LARGE(SUBTOTAL(9,OFFSET($D3:$F3,0,{0,3,6,9,12,15})),{1,2,3,4}),V3)

**shanshine** · 03-07-2013, 10:53 AM

It was almost perfect....can you see my post below

**shanshine** · 03-07-2013, 11:03 AM

Almost perfect.....

On the formula for V3 I need it to count the race if they attended. I need it to base attendance off of Colums that are marked for with an S for example for Racer 1 the formula should calculate that he was at 5 races bases on there being a value in F3, I3, L3, O3, R3 and since there is no value in U3 it should know that he was at 5 races. If I take the values out of P3 and Q3 (because that could actually happen) it takes his bonus points down to 25 even with having a value in R3. Does that make sense?

Basically they get bonus points for showing up and teching in....the points for teching in are in the Column title "S"