More effective rank function

Good afternoon

I am trying to create a formula to save someone manually identifying the position from the data.
The rank formula doesn't allow me to achieve this as it puts the 4th child in position 4 and misses 3.

No Level Vault Club DOB Vault Bars Beam Floor Total Position
1 L3 child1 club1 2005 10.00 10.00 10.00 10.00 40 2
2 L3 child2 club1 2005 10.00 10.00 10.00 10.00 40 2
3 L3 child3 club2 2005 2.00 1.00 1.00 1.00 5 1
4 L3 child4 club2 2005 10.00 10.00 10.00 20.00 50 3

I have seen a thread on here, that appears to provide an answer to someone else, but I cannot get that working for my scenario, although it is also more complex as it provides the additional text to say 1st, 2nd, 3rd. I have played around with it for a couple of days, but I just can't get it working.

I am using excel mac 2011, could that be the problem?

I'd appreciate some advice, thanks in advance, Sarah

Can you please provide a sample file reflecting your desired outcome?

Mr Shorty

I do indeed need to sort first. And your solution works a treat.

I cannot believe I was trying to overcomplicate it by trying to use functions with the RANK function, when actually I should have just gone back to basics! I feel a little silly now.

Thanks So much

Sarah

hoyasaxa215 - thanks for your response too.