Scoring Formula helop
 

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??

Scoring Formula helop
 

Here's a structure I've used befo

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 sco

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

The formula for the overall ranking would be:

=RANK(D4,Score,0)

HTH
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Andrew" wrote:

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??

Scoring Formula helop
 

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)))

Here's one way.

Suppose the times are in O$4:O$9 and the balloons in P$4:P$9.

Using Q as a helper column, put
=1000*P4+O4
in Q4 and copy down. This makes a combined total of the two criteria. If
the maximum time can exceed 1000 (in whatever units are being used), then
use a bigger number instead of 1000.

To put the score in column R, enter
=MAX(0,10*(4-RANK(Q4,$Q$4:$Q$9)))
in R4 and copy down.

This might not give what you want when there's a tie for both balloons and
time.

Scoring Formula helop
 

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

andrew

"MyVeryOwnSelf" wrote:

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)))

Here's one way.

Suppose the times are in O$4:O$9 and the balloons in P$4:P$9.

Using Q as a helper column, put
=1000*P4+O4
in Q4 and copy down. This makes a combined total of the two criteria. If
the maximum time can exceed 1000 (in whatever units are being used), then
use a bigger number instead of 1000.

To put the score in column R, enter
=MAX(0,10*(4-RANK(Q4,$Q$4:$Q$9)))
in R4 and copy down.

This might not give what you want when there's a tie for both balloons and
time.