Rounding down decimals

JoePie

Board Regular
Joined
Apr 24, 2008
Messages
56
This is more for aesthetics than anything.

I'm copying a column range containing numbers with varying decimals from one worksheet to another. The new worksheet and column are set to have no more than two decimals places in the cells through the formatting options.

However, numbers stay showing their original amount of decimal places, so I tried hard-coding the format with:

Code:
Sheets("Toolset Scorecard").Columns("G:G").NumberFormat = "0.00"

But I'm still in the same situation.
Any ideas?
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Are you just copying values (rather than everything including formatting)?

If they are not changing when you apply new formatting, it looks to me that they are text values rather than proper numerics - try selecting the column and going Data>textToColumns>Finish (no need to do anything else) and see if this emans you can now apply formatting to them.
 

JoePie

Board Regular
Joined
Apr 24, 2008
Messages
56
Problem remains.

The cells use VLOOKUPS to get their numbers from the other sheet, which is a value that is copied from another sheet that is obtained from a formula.

i.e.
Different File = Raw Data copied and "paste specialed" into Sheet 1
Sheet 1 = Formula in cell
Sheet 2 = Value copied and "paste specialed" from Sheet 1
Sheet 3 = VLOOKUP to get value from Sheet 2

I guess I could've phrased my initial query better.
 

JoePie

Board Regular
Joined
Apr 24, 2008
Messages
56

ADVERTISEMENT

No, all the formulas work fine.
I was just explaining how the where the numbers come from in case they change between text and numbers at any point.

On what I've dubbed "Sheet 3" The VLOOKUP is equal to 79.1233333111132 and I've manually added '%' because for whatever reason I also couldn't put change the format to a percentage. And what I'm trying to do is round down the above number so it appears as 79.12 in the cell.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
So your VLOOKUP is returning 79.1233333111132 and you want to round this down to 79.12 for display in the cell? If this isn't hapopening, then it does sound to me like Excel thinks it is a text value. Try using exactly the same Vlookup but using this as the formula:

=--VLOOKUP()

and see if you can now round the cell.
 

JoePie

Board Regular
Joined
Apr 24, 2008
Messages
56

ADVERTISEMENT

Nope.
Didn't make a difference other than causing the VLOOKUP to also be seen as an inconsistent formula.

Code:
=--VLOOKUP(D13&"Avail"&C13,MICAT!$A:$C,3,FALSE)&"%"

Thats my VLOOKUP, for whatever its worth.

D13 and C13 both contain text, so I would be looking up something like MIMiiAvail1263 on the MICAT sheet.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
If you concatenate text onto the end of your vlookup formula it converts the return on the Vlookup to Text, so formatting is never going to be able to amend the display.

You need to use:

=VLOOKUP(D13&"Avail"&C13,MICAT!$A:$C,3,FALSE)/100

and format this as a % (using Format>Cells).
 

JoePie

Board Regular
Joined
Apr 24, 2008
Messages
56
That did the job.
Cheers.

Now I have to spend the rest of my day doing actual work.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,214
Messages
5,594,882
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top