Peremanently removing decimal places

[jon]

Hi,
I have a price list that displays all prices to 2 decimal places, but some
prices are actually to 8 or 9.

I have copied all the fields and done a Paste Special Values, which has
removed the calculations, but I now need to change all the fields to
actually be 2 decimal places, not just to display 2 decimal places.

Is there a way of doing this ?

Thanks

Jon

 

[Mike H]

Jon,

but I now need to change all the fields to
actually be 2 decimal places, not just to display 2 decimal places.

And if a number doesn't have any decimal part what do you want to display
..00 or something else?

If it's .oo then format as a number with 2 decimal places.

Mike

 

[jon]

Hi Mike,
Thanks for your reply

Formatting a field in Excel doesn't actually change the data that it holds,
it just changes how it is displayed.

So for example, if field A1 holds a value 7.569585445 then formatting it as
a number with 2 dp willl display as 7.57 but it will still really be
7.569585445 .

So if that value is the price for one widget, and in cell C1 I multiply it
by 1000 to get the price for 1000 it will show as 7569.59 but it should be
7570.

The products we sell are sold in the tens of thousands, so it can soon show
significant differences in price.

Do you have any further suggestions ?

Jon

 

[Mike H]

Hi,

Yes now I understand the question, try this

=ROUND(A1,2)

7.569585445 now becomes 7.57 and multiplying by 1000 = 7570

Mike

 

[jon]

Thanks Mike,
I am aware of the Round function, but how would I apply it to thousands of
fields (without lots of manual work)?

Is there a way of rounding all fields that are selected like you can with
the format painter ?

So far, the only way I can find is to copy the woksheet, then delete all the
prices (so I titles and colour layout etc) then link field A1 to =
ROUND(SHEET1!A1,2) then drag this accross the worksheet.

But this is still time consuming when I have so many thousands of worksheets
to do.

Thanks


Jon

 

[David Biddulph]

Another option if you have set to display 2 decimal places is to use the
"precision as displayed" option, but be careful that it doesn't upset other
data, and you may want to switch the option back again after you've used it
and saved the new data.

 

[John Mansfield]

One option would be to use a macro like the one below to mass-update formulas
to include the rounding function:

Sub Add_Rounding()

Dim cellRange As Range
Dim Rng As Range
Dim cellFormula As String

On Error Resume Next

Set cellRange = Range("A1:C10").SpecialCells(xlCellTypeFormulas)

For Each Rng In cellRange

cellFormula = Mid(Rng.Formula, 2, 1024)
If InStr(UCase(cellFormula), UCase("Round")) = 0 Then
Rng.Formula = "=round(" & cellFormula & ",0)"
End If

Next Rng

End Sub

 

[jon]

Hi John,
I really like this option. ( and it works like a dream)

Is it possible to paste values as well for the same cells ,so any fields
that are still formula fields get changed to actual values ?


Thanks

Jon

 

[Nicole Garaty]

Hi Guys

I hope you can now help me. I have a complex formular for the File Name of a document that is made up of 4 parts.

=CONCATENATE(D8,"-",(LOOKUP(E8,lookUp!$A$3:$B$10)),"-",F8,"-",(SUBSTITUTE(G8,".","")))

G8 is the revision of a document and I want it to display without the decimal.

eg, 0.5 - 05
1.1 - 11
2.0 - 20

The above work for all numbers except the whole numbers, ie. 1.0, 2.0. For these numbers the formula only displays 1 or 2.

eg. revision - 0.2
Displays > SA99-ADS-PPL-0000202-02

revision - 1.2
Displays > SA99-PSP-REP-0000036-12

revision - 1.0
Displays > SA99-PSP-PLN-0000413-1

Please HELP!!!!!

Nicole

 

[alanglloyd]

Hi Guys

I hope you can now help me.  I have a complex formular for the File Name of a document that is made up of 4 parts.

=CONCATENATE(D8,"-",(LOOKUP(E8,lookUp!$A$3:$B$10)),"-",F8,"-",(SUBSTITUTE(G­8,".","")))

G8 is the revision of a document and I want it to display without the decimal.

eg, 0.5  - 05
    1.1  - 11
    2.0  - 20

The above work for all numbers except the whole numbers, ie. 1.0, 2.0.  For these numbers the formula only displays 1 or 2.

eg.  revision - 0.2
     Displays > SA99-ADS-PPL-0000202-02

     revision - 1.2
     Displays > SA99-PSP-REP-0000036-12

     revision - 1.0
     Displays > SA99-PSP-PLN-0000413-1

Replace G8 in your formula with TEXT(G8,"0.0")

Alan Lloyd

 

[Ron Rosenfeld]

Hi Guys

I hope you can now help me. I have a complex formular for the File Name of a document that is made up of 4 parts.

=CONCATENATE(D8,"-",(LOOKUP(E8,lookUp!$A$3:$B$10)),"-",F8,"-",(SUBSTITUTE(G8,".","")))

G8 is the revision of a document and I want it to display without the decimal.

eg, 0.5 - 05
1.1 - 11
2.0 - 20

The above work for all numbers except the whole numbers, ie. 1.0, 2.0. For these numbers the formula only displays 1 or 2.

eg. revision - 0.2
Displays > SA99-ADS-PPL-0000202-02

revision - 1.2
Displays > SA99-PSP-REP-0000036-12

revision - 1.0
Displays > SA99-PSP-PLN-0000413-1

Please HELP!!!!!

Nicole

I think if you replace:

(SUBSTITUTE(G8,".",""))

with

TEXT(G8*10,"00")

you will get the result you are looking for.

 

[joeu2004]

I hope you can now help me.  I have a complex formular
for the File Name of a document that is made up of 4
parts.
=CONCATENATE(D8,"-",(LOOKUP(E8,lookUp!$A$3:$B$10)),"-",
F8,"-",(SUBSTITUTE(G8,".",""))) [....]
The above work for all numbers except the whole numbers

Try:

=D8 & "-" & LOOKUP(E8,lookUp!$A$3:$B$10) & "-" & F8 & "-" &
TEXT(G8*10,"00")

This assumes that you will never have a 2-digit subrevision number,
e.g. 2.12. If you might, it would be prudent to use
TEXT(G8*100,"000") from the beginning. Otherwise, 212 becomes
ambiguous; it might be 21.2 or 2.12.