Poijnts for ranking spread

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Noob Jedi - 28 Aug 2007 16:08 GMT

Assume contest list with varying contestants. Now however they fall
into their places, the ranks will be have 35 places total. There are
more contestants, but we are only concerned with the final 35.

To kind of flip it around:
Assume I made a list with predictions of what the top 35 places would
be. I am receiving points based on how accurate my predictions are
according to the actual results. If I made a prediction that
contestant #1 was going to make it to 7th place and he was actually
7th place, I would get awarded the most points possible, say 15. But
let's say contestant #1 didn't get 7th place. Assume he got 8th
place.
Then I would be 1 place off so my points would be 14 instead of 15. I
guess the scenario would be drawn into 3 or 4 columns as follows:

A1:A35 = Contestant Rankings (1 - 35)
B1:B35 = My predictions of contestant rankings
C1:C35 = The points awarded for each my guesses

How I would I go about making this scenario happen?

Reply to this Message

Pete_UK - 28 Aug 2007 17:46 GMT

Assuming that you don't want negative points if your predictions are
more than 15 places adrift, then try this in C1:

=MAX(0,15-ABS(B1-A1))

and copy down to C35.

Hope this helps.

Pete

> Assume contest list with varying contestants. Now however they fall
> into their places, the ranks will be have 35 places total. There are
[quoted text clipped - 16 lines]
>
> How I would I go about making this scenario happen?

Reply to this Message

Noob Jedi - 28 Aug 2007 22:15 GMT

> Assuming that you don't want negative points if your predictions are
> more than 15 places adrift, then try this in C1:
[quoted text clipped - 29 lines]
>
> - Show quoted text -

I think you're somewhat missing what I'm trying to do. All your
formula simply did was show points in reverse from 14 - 1 all the way
down. Let me illustrate a sample for you to see what I mean.

Actual Results:
A1 = Contestant #4
A2 = Contestant #3
A3 = Contestant #5
A4 = Contestant #1
A5 = Contestant #2

My predictions
B1 = Contestant #2
B2 = Contestant #3
B3 = Contestant #4
B4 = Contestant #1
B5 = Contestant #5

Points Awarded are between 1 and 5 for accuracy of the ranks:
C1 = 1 point (due to guessing #2 5 ranks away)
C2 = 5 points (because it was dead on)
C3 = 4 points (because it was 1 off)
C4 = 5 points (because it was dead on)
C5 = 3 points (because it was 1 off)

Reply to this Message

Pete_UK - 28 Aug 2007 23:10 GMT

I would suggest that you use column A for the contestant name, B for
the actual ranking, and C for your predicted ranking, so that you
would have something like:

Alan 2 3
Barry 7 4
Colin 3 5
David 6 6
Edward 1 2
Frank 5 7
George 4 1
and so on ...

and then put this formula in D1:

=MAX(0,15-ABS(c1-b1))

and copy down.

This should give you 14 (1 place difference), 12 (3 place
differences), 13, 15, 14, 13, 12 etc, which is what you described
several times in your multiple postings. Is this not what you want
now?

Pete

> I think you're somewhat missing what I'm trying to do. All your
> formula simply did was show points in reverse from 14 - 1 all the way
[quoted text clipped - 22 lines]
>
> - Show quoted text -

Reply to this Message

Noob Jedi - 29 Aug 2007 01:43 GMT

> I would suggest that you use column A for the contestant name, B for
> the actual ranking, and C for your predicted ranking, so that you
[quoted text clipped - 50 lines]
>
> - Show quoted text -

Sorry about the multiple post. I got fustrated that my first post
wasn't getting any help, so I posted again in a different area to see
if other users there could help me.

This kind of works. I say kind of because your formula works with
numerical values and I was planning on using text (names of the
contestants). I wanted to see if I could award points based on how far
away each value is in rows from the actual results.

Reply to this Message

Pete_UK - 29 Aug 2007 02:04 GMT

Okay, I think you want something like this (although it involves more
typing for you):

Dave Frank 13
Alan Colin 12
Frank Alan 14
George Dave 12
Colin Barry 14
Barry George 13
Edward Edward 15
and so on ...

The formula in C1 is:

=MAX(15-ABS(MATCH(B1,A$1:A$35,0)-ROW()))

to cover your 35 contestants - copy this down to C35. You must ensure
that there are no duplicate names in the list, so if you have two
Daves then call them Dave_A and Dave_B etc.

Hope this helps.

Pete

> Sorry about the multiple post. I got fustrated that my first post
> wasn't getting any help, so I posted again in a different area to see
[quoted text clipped - 6 lines]
>
> - Show quoted text -

Reply to this Message

Noob Jedi - 29 Aug 2007 13:35 GMT

> Okay, I think you want something like this (although it involves more
> typing for you):
[quoted text clipped - 32 lines]
>
> - Show quoted text -

Thank you for being so patient with me.
This is pretty much what I needed. Although, the only weird thing is,
plugging it into my existing spreadsheet, it gives me weird numbers.
But that's ok, I'll try to figure it out. If not, I'll ask you again.

Thanks again.

Reply to this Message

Pete_UK - 29 Aug 2007 13:49 GMT

Thanks for feeding back - I'm not sure what "weird numbers" are,
though.

Pete

> Thank you for being so patient with me.
> This is pretty much what I needed. Although, the only weird thing is,
[quoted text clipped - 4 lines]
>
> - Show quoted text -

Reply to this Message

Noob Jedi - 29 Aug 2007 14:04 GMT

> Thanks for feeding back - I'm not sure what "weird numbers" are,
> though.
[quoted text clipped - 11 lines]
>
> - Show quoted text -

Lol, yeah, I just spent a little time with it. I found out that it has
a lot to do with the grid placement for some reason. My spreadsheet
starts on C6:C40 and basing it off of B6:B40. Well, your formula
worked like a charm, assuming it started on C1 instead of C6 for some
reason. So I kept the formulas as is and extended the array down to
B40 and C40. This is the final formula that I came up with, including
isna and 0, because for some reason it still read negative numbers:

=IF(ISNA(MAX(15-ABS(MATCH(C6,$B$1:$B$40,0)-ROW()))),0,IF((MAX(15-
ABS(MATCH(C6,$B$1:$B$40,0)-ROW())))<0,0,MAX(15-ABS(MATCH(C6,$B$1:$B
$40,0)-ROW()))))

Reply to this Message

Pete_UK - 29 Aug 2007 16:08 GMT

Glad to hear you got it working how you wanted it in the end.

Pete

> > Thanks for feeding back - I'm not sure what "weird numbers" are,
> > though.
[quoted text clipped - 25 lines]
>
> - Show quoted text -

Reply to this Message

Rate this thread: