Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding a Rounded Number
I'm working in Excel 2003 - VBA.
OldRate is a variable whose value comes from user input into a cell. OldRate=Range("A1") I am multiplying that variable by 1.075 and need to round up the result to two decimal places. NewRate=Application.Worksheetfunction.Round(OldRat e * 1.075, 2) Then I need to multiply that rounded result by .25 and round it up to two decimal places. AdjustedNewRate=Application.Worksheetfunction.Roun d(NewRate * .25, 2) If $27.11 is the old rate, I would expect that $27.11 * 1.075 = $29.14 (rounded). And $29.14 * .25 = $7.29 (rounded). However, I am getting $7.28 in VBA. How do I get VBA to give me $7.29? TIA. -- Ken Hudson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding a Rounded Number
Use worksheetfunction.ceiling to round up. Round rounds to the nearest not up.
-- Charles Chickering "A good example is twice the value of good advice." "Ken Hudson" wrote: I'm working in Excel 2003 - VBA. OldRate is a variable whose value comes from user input into a cell. OldRate=Range("A1") I am multiplying that variable by 1.075 and need to round up the result to two decimal places. NewRate=Application.Worksheetfunction.Round(OldRat e * 1.075, 2) Then I need to multiply that rounded result by .25 and round it up to two decimal places. AdjustedNewRate=Application.Worksheetfunction.Roun d(NewRate * .25, 2) If $27.11 is the old rate, I would expect that $27.11 * 1.075 = $29.14 (rounded). And $29.14 * .25 = $7.29 (rounded). However, I am getting $7.28 in VBA. How do I get VBA to give me $7.29? TIA. -- Ken Hudson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding a Rounded Number
I get 7.29 as my result from using your information. Try to use the
RoundUp() function instead perhaps?? HTH -- Regards, Zack Barresse, aka firefytr "Ken Hudson" wrote in message ... I'm working in Excel 2003 - VBA. OldRate is a variable whose value comes from user input into a cell. OldRate=Range("A1") I am multiplying that variable by 1.075 and need to round up the result to two decimal places. NewRate=Application.Worksheetfunction.Round(OldRat e * 1.075, 2) Then I need to multiply that rounded result by .25 and round it up to two decimal places. AdjustedNewRate=Application.Worksheetfunction.Roun d(NewRate * .25, 2) If $27.11 is the old rate, I would expect that $27.11 * 1.075 = $29.14 (rounded). And $29.14 * .25 = $7.29 (rounded). However, I am getting $7.28 in VBA. How do I get VBA to give me $7.29? TIA. -- Ken Hudson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding a Rounded Number
When I used your exact, I got 7.29 but when I did it your way with vba, I
got 7.28 but this one liner=7.29 MsgBox Round(Range("a1") * 1.075 * 0.25, 2) -- Don Guillett SalesAid Software "Ken Hudson" wrote in message ... I'm working in Excel 2003 - VBA. OldRate is a variable whose value comes from user input into a cell. OldRate=Range("A1") I am multiplying that variable by 1.075 and need to round up the result to two decimal places. NewRate=Application.Worksheetfunction.Round(OldRat e * 1.075, 2) Then I need to multiply that rounded result by .25 and round it up to two decimal places. AdjustedNewRate=Application.Worksheetfunction.Roun d(NewRate * .25, 2) If $27.11 is the old rate, I would expect that $27.11 * 1.075 = $29.14 (rounded). And $29.14 * .25 = $7.29 (rounded). However, I am getting $7.28 in VBA. How do I get VBA to give me $7.29? TIA. -- Ken Hudson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding a Rounded Number
Hi Ken,
Dim your variables as Double, not Single -- Kind regards, Niek Otten Microsoft MVP - Excel "Ken Hudson" wrote in message ... | I'm working in Excel 2003 - VBA. | | OldRate is a variable whose value comes from user input into a cell. | OldRate=Range("A1") | | I am multiplying that variable by 1.075 and need to round up the result to | two decimal places. | NewRate=Application.Worksheetfunction.Round(OldRat e * 1.075, 2) | | Then I need to multiply that rounded result by .25 and round it up to two | decimal places. | AdjustedNewRate=Application.Worksheetfunction.Roun d(NewRate * .25, 2) | | If $27.11 is the old rate, I would expect that $27.11 * 1.075 = $29.14 | (rounded). | And $29.14 * .25 = $7.29 (rounded). | | However, I am getting $7.28 in VBA. | | How do I get VBA to give me $7.29? | | TIA. | | -- | Ken Hudson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding a Rounded Number
Hi Charles,
I don't want to round everything up the next hundreth. For example, I want to round 7.144 to 7.14 and 7.145 to 7.15. Ceiling doesn't look like the way to get there. OldRate = 27.11 NewRate = Application.WorksheetFunction.Round(OldRate * 1.075, 2) NewRate = Application.WorksheetFunction.Round(NewRate * 0.25, 2) If I put the above code in a VBA module, I get 7.28 and I want 7.29. -- Ken Hudson "Charles Chickering" wrote: Use worksheetfunction.ceiling to round up. Round rounds to the nearest not up. -- Charles Chickering "A good example is twice the value of good advice." "Ken Hudson" wrote: I'm working in Excel 2003 - VBA. OldRate is a variable whose value comes from user input into a cell. OldRate=Range("A1") I am multiplying that variable by 1.075 and need to round up the result to two decimal places. NewRate=Application.Worksheetfunction.Round(OldRat e * 1.075, 2) Then I need to multiply that rounded result by .25 and round it up to two decimal places. AdjustedNewRate=Application.Worksheetfunction.Roun d(NewRate * .25, 2) If $27.11 is the old rate, I would expect that $27.11 * 1.075 = $29.14 (rounded). And $29.14 * .25 = $7.29 (rounded). However, I am getting $7.28 in VBA. How do I get VBA to give me $7.29? TIA. -- Ken Hudson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding a Rounded Number
Hi Zach,
If you put this code in a module, don't you get 7.28? OldRate = 27.11 NewRate = Application.WorksheetFunction.Round(OldRate * 1.075, 2) NewRate = Application.WorksheetFunction.Round(NewRate * 0.25, 2) -- Ken Hudson "Zack Barresse" wrote: I get 7.29 as my result from using your information. Try to use the RoundUp() function instead perhaps?? HTH -- Regards, Zack Barresse, aka firefytr "Ken Hudson" wrote in message ... I'm working in Excel 2003 - VBA. OldRate is a variable whose value comes from user input into a cell. OldRate=Range("A1") I am multiplying that variable by 1.075 and need to round up the result to two decimal places. NewRate=Application.Worksheetfunction.Round(OldRat e * 1.075, 2) Then I need to multiply that rounded result by .25 and round it up to two decimal places. AdjustedNewRate=Application.Worksheetfunction.Roun d(NewRate * .25, 2) If $27.11 is the old rate, I would expect that $27.11 * 1.075 = $29.14 (rounded). And $29.14 * .25 = $7.29 (rounded). However, I am getting $7.28 in VBA. How do I get VBA to give me $7.29? TIA. -- Ken Hudson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding a Rounded Number
this gives me 7.29
Option Explicit Dim newrate As Double, oldrate As Double Sub test() oldrate = 27.11 newrate = _ Application.WorksheetFunction.Round(Application.Wo rksheetFunction.Round(oldrate _ * 1.075, 2) * 0.25, 2) Debug.Print newrate End Sub -- Gary "Ken Hudson" wrote in message ... I'm working in Excel 2003 - VBA. OldRate is a variable whose value comes from user input into a cell. OldRate=Range("A1") I am multiplying that variable by 1.075 and need to round up the result to two decimal places. NewRate=Application.Worksheetfunction.Round(OldRat e * 1.075, 2) Then I need to multiply that rounded result by .25 and round it up to two decimal places. AdjustedNewRate=Application.Worksheetfunction.Roun d(NewRate * .25, 2) If $27.11 is the old rate, I would expect that $27.11 * 1.075 = $29.14 (rounded). And $29.14 * .25 = $7.29 (rounded). However, I am getting $7.28 in VBA. How do I get VBA to give me $7.29? TIA. -- Ken Hudson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding a Rounded Number
sub oneliner()
MsgBox Round(Range("a1") * 1.075 * 0.25, 2) or test MsgBox round(Round(Range("a1") * 1.075,2) * 0.25, 2) end sub -- Don Guillett SalesAid Software "Ken Hudson" wrote in message ... Hi Don, I'm not sure the one-liner would give the result I needed. The calculation is payroll related and I need to round the first reponse before multiplying again. If I get three decimals in the first calculation and multiply that by another number without having rounded the first answer, wouldn't I get a different answer than if I had rounded the first calculation? For example, if I get 1.549 in the first calcuation and multiply by 1.1, then: 1.549 * 1.1 = 1.7039 rounded to 1.70 If I round the 1.549 to 1.55, then: 1.55 * 1.1 = 1.705 rounded to 1.71 -- Ken Hudson "Don Guillett" wrote: You didn't like the one liner. sub oneliner() MsgBox Round(Range("a1") * 1.075 * 0.25, 2) end sub -- Don Guillett SalesAid Software "Ken Hudson" wrote in message ... Nick, That was it - the variable needed to be double precision - although I don't know why. Thanks for the early Christmas present! Warmest regards.... -- Ken Hudson "Niek Otten" wrote: Hi Ken, Dim your variables as Double, not Single -- Kind regards, Niek Otten Microsoft MVP - Excel "Ken Hudson" wrote in message ... | I'm working in Excel 2003 - VBA. | | OldRate is a variable whose value comes from user input into a cell. | OldRate=Range("A1") | | I am multiplying that variable by 1.075 and need to round up the result to | two decimal places. | NewRate=Application.Worksheetfunction.Round(OldRat e * 1.075, 2) | | Then I need to multiply that rounded result by .25 and round it up to two | decimal places. | AdjustedNewRate=Application.Worksheetfunction.Roun d(NewRate * .25, 2) | | If $27.11 is the old rate, I would expect that $27.11 * 1.075 = $29.14 | (rounded). | And $29.14 * .25 = $7.29 (rounded). | | However, I am getting $7.28 in VBA. | | How do I get VBA to give me $7.29? | | TIA. | | -- | Ken Hudson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding a Rounded Number
MsgBox round(Round(Range("a1") * 1.075,2) * 0.25, 2)=7.28
MsgBox Application.Round(Application.Round(Range("e1") * 1.075, 2) * 0.25, 2)=7.29 -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... sub oneliner() MsgBox Round(Range("a1") * 1.075 * 0.25, 2) or test MsgBox round(Round(Range("a1") * 1.075,2) * 0.25, 2) end sub -- Don Guillett SalesAid Software "Ken Hudson" wrote in message ... Hi Don, I'm not sure the one-liner would give the result I needed. The calculation is payroll related and I need to round the first reponse before multiplying again. If I get three decimals in the first calculation and multiply that by another number without having rounded the first answer, wouldn't I get a different answer than if I had rounded the first calculation? For example, if I get 1.549 in the first calcuation and multiply by 1.1, then: 1.549 * 1.1 = 1.7039 rounded to 1.70 If I round the 1.549 to 1.55, then: 1.55 * 1.1 = 1.705 rounded to 1.71 -- Ken Hudson "Don Guillett" wrote: You didn't like the one liner. sub oneliner() MsgBox Round(Range("a1") * 1.075 * 0.25, 2) end sub -- Don Guillett SalesAid Software "Ken Hudson" wrote in message ... Nick, That was it - the variable needed to be double precision - although I don't know why. Thanks for the early Christmas present! Warmest regards.... -- Ken Hudson "Niek Otten" wrote: Hi Ken, Dim your variables as Double, not Single -- Kind regards, Niek Otten Microsoft MVP - Excel "Ken Hudson" wrote in message ... | I'm working in Excel 2003 - VBA. | | OldRate is a variable whose value comes from user input into a cell. | OldRate=Range("A1") | | I am multiplying that variable by 1.075 and need to round up the result to | two decimal places. | NewRate=Application.Worksheetfunction.Round(OldRat e * 1.075, 2) | | Then I need to multiply that rounded result by .25 and round it up to two | decimal places. | AdjustedNewRate=Application.Worksheetfunction.Roun d(NewRate * .25, 2) | | If $27.11 is the old rate, I would expect that $27.11 * 1.075 = $29.14 | (rounded). | And $29.14 * .25 = $7.29 (rounded). | | However, I am getting $7.28 in VBA. | | How do I get VBA to give me $7.29? | | TIA. | | -- | Ken Hudson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding a Rounded Number
Thanks Don.
Merry Christmas! -- Ken Hudson "Don Guillett" wrote: MsgBox round(Round(Range("a1") * 1.075,2) * 0.25, 2)=7.28 MsgBox Application.Round(Application.Round(Range("e1") * 1.075, 2) * 0.25, 2)=7.29 -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... sub oneliner() MsgBox Round(Range("a1") * 1.075 * 0.25, 2) or test MsgBox round(Round(Range("a1") * 1.075,2) * 0.25, 2) end sub -- Don Guillett SalesAid Software "Ken Hudson" wrote in message ... Hi Don, I'm not sure the one-liner would give the result I needed. The calculation is payroll related and I need to round the first reponse before multiplying again. If I get three decimals in the first calculation and multiply that by another number without having rounded the first answer, wouldn't I get a different answer than if I had rounded the first calculation? For example, if I get 1.549 in the first calcuation and multiply by 1.1, then: 1.549 * 1.1 = 1.7039 rounded to 1.70 If I round the 1.549 to 1.55, then: 1.55 * 1.1 = 1.705 rounded to 1.71 -- Ken Hudson "Don Guillett" wrote: You didn't like the one liner. sub oneliner() MsgBox Round(Range("a1") * 1.075 * 0.25, 2) end sub -- Don Guillett SalesAid Software "Ken Hudson" wrote in message ... Nick, That was it - the variable needed to be double precision - although I don't know why. Thanks for the early Christmas present! Warmest regards.... -- Ken Hudson "Niek Otten" wrote: Hi Ken, Dim your variables as Double, not Single -- Kind regards, Niek Otten Microsoft MVP - Excel "Ken Hudson" wrote in message ... | I'm working in Excel 2003 - VBA. | | OldRate is a variable whose value comes from user input into a cell. | OldRate=Range("A1") | | I am multiplying that variable by 1.075 and need to round up the result to | two decimal places. | NewRate=Application.Worksheetfunction.Round(OldRat e * 1.075, 2) | | Then I need to multiply that rounded result by .25 and round it up to two | decimal places. | AdjustedNewRate=Application.Worksheetfunction.Roun d(NewRate * .25, 2) | | If $27.11 is the old rate, I would expect that $27.11 * 1.075 = $29.14 | (rounded). | And $29.14 * .25 = $7.29 (rounded). | | However, I am getting $7.28 in VBA. | | How do I get VBA to give me $7.29? | | TIA. | | -- | Ken Hudson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding a Rounded Number
Hope it helped. Merry xmas too.
-- Don Guillett SalesAid Software "Ken Hudson" wrote in message ... Thanks Don. Merry Christmas! -- Ken Hudson "Don Guillett" wrote: MsgBox round(Round(Range("a1") * 1.075,2) * 0.25, 2)=7.28 MsgBox Application.Round(Application.Round(Range("e1") * 1.075, 2) * 0.25, 2)=7.29 -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... sub oneliner() MsgBox Round(Range("a1") * 1.075 * 0.25, 2) or test MsgBox round(Round(Range("a1") * 1.075,2) * 0.25, 2) end sub -- Don Guillett SalesAid Software "Ken Hudson" wrote in message ... Hi Don, I'm not sure the one-liner would give the result I needed. The calculation is payroll related and I need to round the first reponse before multiplying again. If I get three decimals in the first calculation and multiply that by another number without having rounded the first answer, wouldn't I get a different answer than if I had rounded the first calculation? For example, if I get 1.549 in the first calcuation and multiply by 1.1, then: 1.549 * 1.1 = 1.7039 rounded to 1.70 If I round the 1.549 to 1.55, then: 1.55 * 1.1 = 1.705 rounded to 1.71 -- Ken Hudson "Don Guillett" wrote: You didn't like the one liner. sub oneliner() MsgBox Round(Range("a1") * 1.075 * 0.25, 2) end sub -- Don Guillett SalesAid Software "Ken Hudson" wrote in message ... Nick, That was it - the variable needed to be double precision - although I don't know why. Thanks for the early Christmas present! Warmest regards.... -- Ken Hudson "Niek Otten" wrote: Hi Ken, Dim your variables as Double, not Single -- Kind regards, Niek Otten Microsoft MVP - Excel "Ken Hudson" wrote in message ... | I'm working in Excel 2003 - VBA. | | OldRate is a variable whose value comes from user input into a cell. | OldRate=Range("A1") | | I am multiplying that variable by 1.075 and need to round up the result to | two decimal places. | NewRate=Application.Worksheetfunction.Round(OldRat e * 1.075, 2) | | Then I need to multiply that rounded result by .25 and round it up to two | decimal places. | AdjustedNewRate=Application.Worksheetfunction.Roun d(NewRate * .25, 2) | | If $27.11 is the old rate, I would expect that $27.11 * 1.075 = $29.14 | (rounded). | And $29.14 * .25 = $7.29 (rounded). | | However, I am getting $7.28 in VBA. | | How do I get VBA to give me $7.29? | | TIA. | | -- | Ken Hudson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number are rounded to thousand, but they now do not foot | Excel Discussion (Misc queries) | |||
Can rounded numbers be summed without rounding errors? | Excel Discussion (Misc queries) | |||
Rounded-off number | Excel Programming | |||
marrying a rounded number to concatenation. | New Users to Excel | |||
showing a rounded number without rounding it | Excel Programming |