Formula formatting

[Bill Smith]

Hello all,
I have a form that I use as a worksheet. I have a currency value (v1) that I
multiply (m1). I then take that unbound number (formatted as currency and 2
decimal places) and multiply by a quantity (q1) for a total dollar value
(unbound2).

[v1] *[m1] = unbound1
unbound1*[q1] = unbound2

The problem i have it that if the true value of unbound1 has 4 decimal
places, [q1] multiplies it using the 4 decimal places.

Example:
$23.26 * 1.125 = $26.1675 (displaying $26.17 - aka Unbound1)
Displayed: $26.17 * 1500 ea = $39255.00
True Value: $26.1675 * 1500 ea = $39251.25
I want to multiply a quantity * the displayed value, not the true value.

Is there any way to do this?

 

[Arvin Meyer [MVP]]

You'll need to force the number to round to 2 decimal places instead of just
formatting to 2 decimal places. I'd use:

Function RoundN(x, N As Integer)
'
' Rounds a number to N decimal places
' Uses arithmatic rounding
' N should be in the range 0-10 for proper results
'
Dim Factor As Long
Factor = 10 ^ N
RoundN = Int(x * Factor + 0.5) / Factor
End Function

 

[Bill Smith]

Hello all,
I have a form that I use as a worksheet. I have a currency value (v1)
that I multiply (m1). I then take that unbound number (formatted as
currency and 2 decimal places) and multiply by a quantity (q1) for a
total dollar value (unbound2).

[v1] *[m1] = unbound1
unbound1*[q1] = unbound2

The problem i have it that if the true value of unbound1 has 4 decimal
places, [q1] multiplies it using the 4 decimal places.

Example:
$23.26 * 1.125 = $26.1675 (displaying $26.17 - aka Unbound1)
Displayed: $26.17 * 1500 ea = $39255.00
True Value: $26.1675 * 1500 ea = $39251.25
I want to multiply a quantity * the displayed value, not the true value.

Is there any way to do this?

You'll need to force the number to round to 2 decimal places instead of
just formatting to 2 decimal places. I'd use:

Function RoundN(x, N As Integer)
'
' Rounds a number to N decimal places
' Uses arithmatic rounding
' N should be in the range 0-10 for proper results
'
Dim Factor As Long
Factor = 10 ^ N
RoundN = Int(x * Factor + 0.5) / Factor
End Function
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks Arvin!