Scoring Formula helop

[Andrew]

I am trying to make an excel file that keeps scores for some events I am
having. The problem that I am having is that in this one event I have 2
variables Balloons and time.

I am trying to make it so that if you have the most balloons then you get 30
points, 2nd most gets 20, 3rd 10, and the rest 0. If there is a tie for
number of balloons I want it to go to time.

For an event that is just comparing time, this is what I have:
=IF(SMALL(O$4:O$9,1)=O4,1,IF(SMALL(O$4:O$9,2)=O4,2,IF(SMALL(O$4:O$9,3)=O4,3,0)))

Can anybody give me a hand with this??

 

[M Kan]

Here's a structure I've used before:

http://www.kan.org/tips/files/scoring_model.xls

Assuming you have two columns, one for balloons (b) and another for seconds
(c), you can calculate a raw score in column D, which would be determined by
balloons rank x 10 + time rank. The time rank should be small enough to
never outweight balloons peformance, but is enough to break ties.

This is the formula for raw score:

=RANK(B4,Balloons,1)*10+RANK(C4,Time,0)

The formula for the overall ranking would be:

=RANK(D4,Score,0)

HTH

 

[Andrew]

Thank you, worked great and helped me optimize other stuff.

andrew