Peremanently removing decimal places
 

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

Peremanently removing decimal places
 

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" wrote:

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

Peremanently removing decimal places
 

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" wrote in message
...
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" wrote:

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

Peremanently removing decimal places
 

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" wrote:

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" wrote in message
...
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" wrote:

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

Peremanently removing decimal places
 

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


"Mike H" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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

Peremanently removing decimal places
 

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.
--
David Biddulph

"jon" wrote in message
...
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


"Mike H" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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

Peremanently removing decimal places
 

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


--
John Mansfield
cellmatrix.net


"jon" wrote:

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


"Mike H" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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

Peremanently removing decimal places
 

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

"John Mansfield" wrote in message
...
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


--
John Mansfield
cellmatrix.net


"jon" wrote:

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


"Mike H" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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

Need Help Removing a Decimal Point in a Formula
 

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




On Thursday, January 22, 2009 6:44 AM jon wrote:

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


On Thursday, January 22, 2009 7:06 AM Mike wrote:

Jon,


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

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

Mike


"jon" wrote:


On Thursday, January 22, 2009 7:30 AM jon wrote:

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" wrote in message
...


On Thursday, January 22, 2009 7:45 AM Mike wrote:

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" wrote:


On Thursday, January 22, 2009 8:02 AM jon wrote:

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


"Mike H" wrote in message
...


On Thursday, January 22, 2009 8:17 AM David Biddulph wrote:

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.
--
David Biddulph

"jon" wrote in message
...


On Thursday, January 22, 2009 8:26 AM JohnMansfiel wrote:

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


--
John Mansfield
cellmatrix.net


"jon" wrote:


On Thursday, January 22, 2009 9:40 AM jon wrote:

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

"John Mansfield" wrote in message
...

Need Help Removing a Decimal Point in a Formula
 

On May 17, 5:43*am, Nicole Garaty wrote:
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

Need Help Removing a Decimal Point in a Formula
 

On Tue, 17 May 2011 04:43:04 GMT, Nicole Garaty wrote:

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.

Need Help Removing a Decimal Point in a Formula
 

On May 16, 9:43*pm, Nicole Garaty wrote:
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.