Hello everyone

I have a golf spreadsheet which at the end of each round calculates where
everyone is in relation to the twelve players.

I am ok with ranking the top set but am struggling with the bottom of the
table where joint positions exist.


The formula firstly checks to see if there are 12 players.

Then - if the cell is equal to top ($F$175) then the player has won and
loses two shots on the next round!
Then - if the cell is equal to second place ($F$175+1) then the player has
come second and loses one shot on the next round!
Then - if the cell is equal to bottom ($F$176) then the player has come last
and will be gifted two shots on the next round!
NOW THE COMPLICATED BIT!!
If the next back was ALWAYS 11th then that would be ok BUT if there is a
joint position then I am stumped for a calculation!

=IF($F$180=12,"",IF(F165=$F$175,"-2",IF(F165=$F$175+1,"-1",IF(F165=$F$176,"+
2",IF(AND(F165=$F$176-1,$F$177=1),"+1","")))))

I would be grateful for any help in working this out.


Thanks


John in North Yorkshire UK

jp

Can you provide some sample data? What's in F165:F177? Where does the
formula go? What's the intended answer for the formula?

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"jp" wrote in message
...
Hello everyone

I have a golf spreadsheet which at the end of each round calculates where
everyone is in relation to the twelve players.

I am ok with ranking the top set but am struggling with the bottom of the
table where joint positions exist.


The formula firstly checks to see if there are 12 players.

Then - if the cell is equal to top ($F$175) then the player has won and
loses two shots on the next round!
Then - if the cell is equal to second place ($F$175+1) then the player has
come second and loses one shot on the next round!
Then - if the cell is equal to bottom ($F$176) then the player has come
last
and will be gifted two shots on the next round!
NOW THE COMPLICATED BIT!!
If the next back was ALWAYS 11th then that would be ok BUT if there is a
joint position then I am stumped for a calculation!


=IF($F$180=12,"",IF(F165=$F$175,"-2",IF(F165=$F$175+1,"-1",IF(F165=$F$176,"+
2",IF(AND(F165=$F$176-1,$F$177=1),"+1","")))))

I would be grateful for any help in working this out.


Thanks


John in North Yorkshire UK

I have a sample sheet if I could send it to you?

I am trying to rank a group of 12 golfers and all goes well UNTIL there is a
tie which makes second to last anything but 11th!!

Thanks


John

Hi John,
That isn't very clear.
Usually...
If you have 12 (or more) golfers, and 12 places (or more) the two golfers
tied, are tied for 11th, and the 13th golfer (or next highest score) is in
12th place.
If there are only 12 golfers there is no 12th place. (assuming the last 2
are tied and no others are tied.)


--

John

johnf202 at hotmail dot com


"jp" wrote in message
...
I have a sample sheet if I could send it to you?

I am trying to rank a group of 12 golfers and all goes well UNTIL there is
a
tie which makes second to last anything but 11th!!

Thanks


John

J

That is exactly correct.

Sorry for the confusion - I realis the last is not always the 12th place but
it will be the Min value in the range.

I know how to track the last place but not the second to last place.

Thanks


John

John

Go ahead and send the sample sheet to me if you want.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"jp" wrote in message
...
I have a sample sheet if I could send it to you?

I am trying to rank a group of 12 golfers and all goes well UNTIL there is
a
tie which makes second to last anything but 11th!!

Thanks


John