Poijnts for ranking spread

[Noob Jedi]

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?

 

[Pete_UK]

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

 

[Noob Jedi]

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






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

 

[Pete_UK]

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

 

[Noob Jedi]

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







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

 

[Pete_UK]

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

 

[Noob Jedi]

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





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

 

[Pete_UK]

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

Pete

 

[Noob Jedi]

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

Pete





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

 

[Pete_UK]

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

Pete