Rounding down decimals

[JoePie]

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:

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

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

 

[Richard Schollar]

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]

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.

 

[Richard Schollar]

Sorry I'm a bit confused now - is it the Vlookup formulas that aren't working?

 

[JoePie]

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]

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]

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

=--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]

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]

That did the job.
Cheers.

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

 

[Richard Schollar]

Sadly the same goes for me too