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

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

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

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

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

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

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

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

Rounding a Rounded Number
 

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

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

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

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

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