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

marshen

New Member
Joined
Apr 4, 2016
Messages
17
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
 

Some videos you may like

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
Joined
Apr 23, 2012
Messages
317
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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
Joined
Apr 4, 2016
Messages
17
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

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
it seems to work,
A3 is 3.14 and A4 is pi rounded to 2 decimals

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">1.00</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">2.00</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3.14</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3.14</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4.00</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5.00</td><td style="text-align: right;;">6</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A4</th><td style="text-align:left">=ROUND(<font color="Blue">PI(<font color="Red"></font>),2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=RANK(<font color="Blue">A1,$A$1:$A$6,1</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

nardagus

Active Member
Joined
Apr 23, 2012
Messages
317
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 4, 2016
Messages
17
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
Joined
Apr 23, 2012
Messages
317
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Nard
 

marshen

New Member
Joined
Apr 4, 2016
Messages
17
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
Joined
Apr 23, 2012
Messages
317
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,441
Members
410,684
Latest member
LakTik
Top