Rounding a Rounded Number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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(OldRate * 1.075, 2)

Then I need to multiply that rounded result by .25 and round it up to two
decimal places.
AdjustedNewRate=Application.Worksheetfunction.Round(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.
 
I get 7.29 as my result from using your information. Try to use the
RoundUp() function instead perhaps??

HTH
 
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)
 
Hi Ken,

Dim your variables as Double, not Single

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| 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(OldRate * 1.075, 2)
|
| Then I need to multiply that rounded result by .25 and round it up to two
| decimal places.
| AdjustedNewRate=Application.Worksheetfunction.Round(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
 
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.
 
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)
 
this gives me 7.29
Option Explicit
Dim newrate As Double, oldrate As Double
Sub test()
oldrate = 27.11
newrate = _
Application.WorksheetFunction.Round(Application.WorksheetFunction.Round(oldrate
_
* 1.075, 2) * 0.25, 2)
Debug.Print newrate
End Sub
 
Hi Ken,

The difference in precision between Single and Double is really quite large.
There is always a chance af a number being right on the edge (or really right off), but the chance of that happening is
substantially reduced if you use Doubles.


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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
| >
| >
| > | 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(OldRate * 1.075, 2)
| > |
| > | Then I need to multiply that rounded result by .25 and round it up to two
| > | decimal places.
| > | AdjustedNewRate=Application.Worksheetfunction.Round(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
| >
| >
| >
 
Hi Niek,
(Sorry about the typo on your name in my last post.)
I guess I still don't understand the problem with using single precision. If
the result of my calculation was 7-8 decimal places, why doesn't single
precision round the answer correctly up to the nearest hundredth?
If you don't have the time to explain, no problem.
Cheers...
 
You didn't like the one liner.

sub oneliner()
MsgBox Round(Range("a1") * 1.075 * 0.25, 2)
end sub
 
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
 
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
 
Thanks Don.
Merry Christmas!

--
Ken Hudson


Don Guillett said:
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
Click to expand...
 
Back
Top