Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
decimal places | Excel Discussion (Misc queries) | |||
Decimal Places | Excel Discussion (Misc queries) | |||
Subtracting two 2-decimal place numbers gives result 13-decimal places? | Excel Worksheet Functions | |||
Decimal Places | Excel Discussion (Misc queries) | |||
FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. | Excel Discussion (Misc queries) |