Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with decimals/percentages
Hi all
ok probably a simple problem, but here goes, basically I have a range of values which I have pulled in from a separate report, however this is in the wrong format for my colleague to use due to the formulas. I'm pretty new to macros and have played around to the stage of copying a named range to a new sheet but the formatting is the problem. The numbers are percentages, however the format needs to be whole numbers, to 2 decimal places and without the percentage sign (so it doesn't work using the percentage format)... examples of the numbers are below: Property 1 0.02800 - 2.80% Property 2 0.00550 - 0.55% Property 3 0.08710 - 8.71% Property 4 0.02910 - 2.91% Property 5 0.01740 - 1.74% Property 6 0.01690 - 1.69% etc These numbers will change each and every quarter, so not sure if a "substitute" formula is the way to go or not, any suggestions gratefully received!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with decimals/percentages
If all of these numbers fall within a specific range and no other format is
required, such as dates, times, etc. Then you can manually format the range as General from the menu bar FormatCellsGeneral. If you have to use code, then, assuming a value is in cell B1, the code: x = CDbl(Range("B1").Value) would convert the percentage to a number with a floating decimal point. You would have to make it a string value by formatting to limit it to two decimal points: x = Format(CDbl(Range("B1").Value), "#0.00" Then you can put the variable x value in a range, like A1 by: Range("A1") = x Maybe this will help, if I haven't confused you. "Stav19" wrote: Hi all ok probably a simple problem, but here goes, basically I have a range of values which I have pulled in from a separate report, however this is in the wrong format for my colleague to use due to the formulas. I'm pretty new to macros and have played around to the stage of copying a named range to a new sheet but the formatting is the problem. The numbers are percentages, however the format needs to be whole numbers, to 2 decimal places and without the percentage sign (so it doesn't work using the percentage format)... examples of the numbers are below: Property 1 0.02800 - 2.80% Property 2 0.00550 - 0.55% Property 3 0.08710 - 8.71% Property 4 0.02910 - 2.91% Property 5 0.01740 - 1.74% Property 6 0.01690 - 1.69% etc These numbers will change each and every quarter, so not sure if a "substitute" formula is the way to go or not, any suggestions gratefully received!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with decimals/percentages
On Mar 25, 11:16*pm, JLGWhiz
wrote: If all of these numbers fall within a specific range and no other format is required, such as dates, times, etc. Then you can manually format the range as General from the menu bar *FormatCellsGeneral. *If you have to use code, then, assuming a value is in cell B1, the code: *x = CDbl(Range("B1").Value) would convert the percentage to a number with a floating decimal point. *You would have to make it a string value by formatting to limit it to two decimal points: x = Format(CDbl(Range("B1").Value), "#0.00" Then you can put the variable x value in a range, like A1 by: Range("A1") = x Maybe this will help, if I haven't confused you. "Stav19" wrote: Hi all ok probably a simple problem, but here goes, basically I have a range of values which I have pulled in from a separate report, however this is in the wrong format for my colleague to use due to the formulas. I'm pretty new to macros and have played around to the stage of copying a named range to a new sheet but the formatting is the problem. The numbers are percentages, however the format needs to be whole numbers, to 2 decimal places and without the percentage sign (so it doesn't work using the percentage format)... examples of the numbers are below: Property 1 * * *0.02800 - 2.80% Property 2 * * *0.00550 - 0.55% Property 3 * * *0.08710 - 8.71% Property 4 * * *0.02910 - 2.91% Property 5 * * *0.01740 - 1.74% Property 6 * * *0.01690 - 1.69% etc These numbers will change each and every quarter, so not sure if a "substitute" formula is the way to go or not, any suggestions gratefully received!!!- Hide quoted text - - Show quoted text - thanks for that, will try that in the workbook, my problem is that I want to show the middle column as whole numbers to 2dp, I'm thinking something to do with multiplying by 100... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem with number format with 2 decimals? | Excel Worksheet Functions | |||
VBA or Macro solution to convert Percentages to decimals | Excel Programming | |||
Decimals problem | Excel Programming | |||
Problem formatting decimals in ComboBox | Excel Programming | |||
Fractions to Decimals problem | Excel Discussion (Misc queries) |