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

#### marshen

##### New Member
Hi all.

Thanks for your help earlier on today.

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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### nardagus

##### Active Member
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

##### New Member
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.

#### AlanY

##### Well-known Member
it seems to work,
A3 is 3.14 and A4 is pi rounded to 2 decimals

Last edited:

#### nardagus

##### Active Member

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

##### New Member
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

##### Active Member

To be honest I prefer index+match over vlookup.

Nard

#### marshen

##### New Member
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

##### Active Member
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

Replies
3
Views
256
Replies
0
Views
154
Replies
7
Views
94
Replies
7
Views
622
Replies
9
Views
233