Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
jon jon is offline
external usenet poster
 
Posts: 13
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
jon jon is offline
external usenet poster
 
Posts: 13
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
jon jon is offline
external usenet poster
 
Posts: 13
Default 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







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default 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







  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 235
Default 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






  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
jon jon is offline
external usenet poster
 
Posts: 13
Default 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







  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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
...




  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default 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


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default 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.

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default 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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
decimal places narp[_2_] Excel Discussion (Misc queries) 2 May 14th 08 09:39 PM
Decimal Places Tafmutt Excel Discussion (Misc queries) 2 March 14th 07 10:22 PM
Subtracting two 2-decimal place numbers gives result 13-decimal places? [email protected] Excel Worksheet Functions 5 March 12th 07 11:38 PM
Decimal Places Rab Swinney Excel Discussion (Misc queries) 5 January 10th 07 08:01 PM
FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. SUKYKITTY Excel Discussion (Misc queries) 3 July 6th 05 01:50 PM


All times are GMT +1. The time now is 12:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"