Ranking numbers - rank isn't working as i need due to decimal places, help please

[marshen]

Hi all.

Thanks for your help earlier on today.

Below is the link to a shot of my spreadsheet.

Cells K23 & K25 are given ranks 2 & 1 respectively. I know this is because the original number is an average way back in another spreadsheet. I want the rank to be judged on the 9.77 only and not 9.7754154254 or whatever it may be and therefore the value I want would be 1 for both K23 & K25, does anyone know how I would do this?

Any help would be appreciated.

Thanks
Mark

https://www.dropbox.com/s/rvrc7658lez27ci/20160404_124154-1.jpg?dl=0

 

[nardagus]

Hello,

When you see 2 digit decimals in a cell it doesn't mean this value has really 2 decimals. If you want to get hard-rounding for values you could use in J column:

=ROUND(what;no_of_decimals)

Nard

 

[marshen]

thanks but unfortunately it isn't working for me.

If you see this picture attached on the drop box link.

https://www.dropbox.com/s/yj8f4dr4rrvvmf8/20160404_133758-1.jpg?dl=0

I'm doing the round function and the information shown is correct but as the value, 9.77 is coming off a different page in the spreadsheet it's making a circular reference and the value is going to 0.00, it's so infuriating.

Thanks for your help

 

[AlanY]

it seems to work,
A3 is 3.14 and A4 is pi rounded to 2 decimals

Excel 2012
	A	B
1	1.00	1
2	2.00	2
3	3.14	3
4	3.14	3
5	4.00	5
6	5.00	6
Sheet1

Cell Formulas
Range	Formula
A4	=ROUND(PI(),2)
B1	=RANK(A1,$A$1:$A$6,1)

 

[nardagus]

You cannot create a formula for a cell with a reference to this cell. That's why you get 0.
Do I get it right that the actual value in J23 comes from Totals!P5? Then your formula should look like this:

=Round(Total!P5;2)

Nard

 

[marshen]

Fantastic that's worked, now just to crack the v look up feature to get this working in a league table format as such.
cheers

 

[nardagus]

Glad I could help.
To be honest I prefer index+match over vlookup.

Nard

 

[marshen]

Now you're just confusing me. This possibly sounds like an easier way to sort everything? As you'll have seen iIve managed to get a rank for each of my drivers and it's just a case of getting them in order. Any assistance would be appreciated?

Thanks mark

 

[nardagus]

No, no. VLOOKUP and combo INDEX+MATCH give you the same result. It's just for me combo is easier (as I learned it before I cracked vlookup ). Besides IMO combo gives you less risk of an error as in VLOOKUP you hard-code number of column while combo works directly on real column addresses which you usually click to get it into a formula.
If you are interested here you will find a nice blog about both: vlookup and combo.


As for sorting. If your drivers ranks change during a month and you want to keep a record of the current ranking than yes. You can use vlookup/combo. To be honest I didn't come up with anything better. Maybe someone else will have a better idea.

Nard