Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding in VBA
Hi Team,
I am facing a problem with rounding a number using VBA in excel. Let me explain with an example. suppose take 2 numbers 23.2067 and 23.2234. Now I need to round these 2 numbers to 2 digits. I am using application.round(number,2) to round up to 2 decimals. I am getting 23.2 and 23.22. But I want 2 digits after the decimal means for 23.2067 i want it to be 23.20. Can it be possible in VBA? when i am trying to do it by text(number,"0.00") its giving me an error sub function not defined. I have one alternative by using split function temp = split(application.round(number,2),".") and by calculating length of temp(1) I can know the number of digits after decimal. If i have only one number after decimal I can concatenate 0 with the number. this is a long process and in my macro this case may come once in 10000 calculatons. So can any body suggest an alternative way or easy way to handle this. Regards, Srinivas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding in VBA
Srinvas,
try something like this. Sub test() With Range("A1") .Value = 23.2067 .NumberFormat = "0.00" End With End Sub -- Hope that helps. Vergel Adriano "srinivas" wrote: Hi Team, I am facing a problem with rounding a number using VBA in excel. Let me explain with an example. suppose take 2 numbers 23.2067 and 23.2234. Now I need to round these 2 numbers to 2 digits. I am using application.round(number,2) to round up to 2 decimals. I am getting 23.2 and 23.22. But I want 2 digits after the decimal means for 23.2067 i want it to be 23.20. Can it be possible in VBA? when i am trying to do it by text(number,"0.00") its giving me an error sub function not defined. I have one alternative by using split function temp = split(application.round(number,2),".") and by calculating length of temp(1) I can know the number of digits after decimal. If i have only one number after decimal I can concatenate 0 with the number. this is a long process and in my macro this case may come once in 10000 calculatons. So can any body suggest an alternative way or easy way to handle this. Regards, Srinivas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding in VBA
23.2067 rounded to 2 decimal places is 23.21 and not 23.20 as in your example
so your not really rounding the number if you really want 23.20 your truncating it. to round the numbers use x = Round(23.2067, 2) to truncate them use x = Val(Left(23.2067, 5)) Mike "srinivas" wrote: Hi Team, I am facing a problem with rounding a number using VBA in excel. Let me explain with an example. suppose take 2 numbers 23.2067 and 23.2234. Now I need to round these 2 numbers to 2 digits. I am using application.round(number,2) to round up to 2 decimals. I am getting 23.2 and 23.22. But I want 2 digits after the decimal means for 23.2067 i want it to be 23.20. Can it be possible in VBA? when i am trying to do it by text(number,"0.00") its giving me an error sub function not defined. I have one alternative by using split function temp = split(application.round(number,2),".") and by calculating length of temp(1) I can know the number of digits after decimal. If i have only one number after decimal I can concatenate 0 with the number. this is a long process and in my macro this case may come once in 10000 calculatons. So can any body suggest an alternative way or easy way to handle this. Regards, Srinivas |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding in VBA
On 4 May 2007 04:42:17 -0700, srinivas wrote:
Hi Team, I am facing a problem with rounding a number using VBA in excel. Let me explain with an example. suppose take 2 numbers 23.2067 and 23.2234. Now I need to round these 2 numbers to 2 digits. I am using application.round(number,2) to round up to 2 decimals. I am getting 23.2 and 23.22. But I want 2 digits after the decimal means for 23.2067 i want it to be 23.20. Can it be possible in VBA? when i am trying to do it by text(number,"0.00") its giving me an error sub function not defined. I have one alternative by using split function temp = split(application.round(number,2),".") and by calculating length of temp(1) I can know the number of digits after decimal. If i have only one number after decimal I can concatenate 0 with the number. this is a long process and in my macro this case may come once in 10000 calculatons. So can any body suggest an alternative way or easy way to handle this. Regards, Srinivas The numeric value of 23.20 IS 23.2 and that's why you see it that way. In VBA, to get the value to display formatted to two decimal places, you use various methods: ?application.WorksheetFunction.Text(26.2,"#.00") 26.20 ?format(26.2,"#.00") 26.20 Both of those methods return strings. In a worksheet cell, of course, you can format the cell to show two decimal places. But the Value will still always be 26.2 --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding in VBA
The VBA function Fix() is equivalent to the worksheet function ROUNDDOWN(,0),
so that Fix(number*100)/100 is equivalent to ROUNDDOWN(,2) without having to know INT(LOG10(number)). Also, From Excel 2000 on, VBA has a Round() function, although it does differ slightly from the worksheet ROUND() function when the number to be rounded is exactly half-way between two rounded numbers (such as .125 to be rounded to 2 decimal places). In the case of such a tie, the worksheet function always rounds up (.13), while the VBA function rounds up or down as needed to produce an even rounded digit (.12). http://en.wikipedia.org/wiki/Unbiase...to-even_method Jerry "Mike H" wrote: 23.2067 rounded to 2 decimal places is 23.21 and not 23.20 as in your example so your not really rounding the number if you really want 23.20 your truncating it. to round the numbers use x = Round(23.2067, 2) to truncate them use x = Val(Left(23.2067, 5)) Mike "srinivas" wrote: Hi Team, I am facing a problem with rounding a number using VBA in excel. Let me explain with an example. suppose take 2 numbers 23.2067 and 23.2234. Now I need to round these 2 numbers to 2 digits. I am using application.round(number,2) to round up to 2 decimals. I am getting 23.2 and 23.22. But I want 2 digits after the decimal means for 23.2067 i want it to be 23.20. Can it be possible in VBA? when i am trying to do it by text(number,"0.00") its giving me an error sub function not defined. I have one alternative by using split function temp = split(application.round(number,2),".") and by calculating length of temp(1) I can know the number of digits after decimal. If i have only one number after decimal I can concatenate 0 with the number. this is a long process and in my macro this case may come once in 10000 calculatons. So can any body suggest an alternative way or easy way to handle this. Regards, Srinivas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding off | Excel Discussion (Misc queries) | |||
Rounding Up | Excel Discussion (Misc queries) | |||
ROUNDING FORMULA =ROUND(B12/$B$10,1) ROUNDING TO HIGH | Excel Worksheet Functions | |||
I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
Worksheet rounding vs VBA rounding | Excel Programming |