help with ranking using closest number to number selected to break a tie

I have created a spreadsheet with a number of formulas linked together from different worksheets. This is for an Australian Football tipping competition where points are allocated for each of the nine games per round. Positive points are gained for a correct tip, negative points for an incorrect tip. After a complete round the winner needs to be decided, if there is a tie of points gained then a tiebreaker needs to be used to work out the winner. This is determined by whoever has chosen the closest to a margin selected. In the worksheet attached round 3 has 3 people who have each got the highest amount of points so an overall winner was determined by being closest to the margin selected for a particular game. The sheet to work off is the leader board sheetTipping draft.xlsx

Originally Posted by MarvinP

Hi Drastic and welcome to the forum,

Find the attached with an answer for you.

Look at the new formula I have and figure it out.

I've added on to the scores a decimal based on the margin. I also changed the formulas to indirect so I could use the names in Col A for the Sheet Name. This way you can simply pull the formulas across the sheet after the games are finished and it should work correctly.

Let me know if you need more explaination.

Marvin,

Thanks for that, I can see how you have used the Indirect function and then added a value to the ABS margin amount to determine the ranking, I knew that I had to do something like that to get it to work. I dont want to have the amounts returned though visible so I will need to move them either to the left somewhere or below and hide them. I have seen a number of similar posts with people trying to rank but not have them in descending or ascending order and most answers for them have had a similar thread.