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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

nardagus

Active Member
Joined
Apr 23, 2012
Messages
316
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,191
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
316
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
316
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
316
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,109,411
Messages
5,528,623
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top