Display currency as calculated

[Zerowaycool]

Gyus I am just about finished but I can't get Excel to display my currency as
the calculated result. I need to display this accurately so I can take the
figures to my accountant. My issue is that when I calculate the GST on an
amount, the calc is fine but is displayed in correctly eg $22.63 / 11 =
2.05727272727273, so I need it to display it as $2.05 not as it does - $2.06
(I am sure the taxman would like this printout). I looked at the TRUNC
command but can't get it to work.

Is there a way to do this in code to a column of numbers where every row has
been summed(horizontally), or is there another type of display style I should
use.

Thanks in advance

Gav

 

[Wouter HM]

Hi Gav,

You have to specify the number of digits remaining
Try:
=Trunc(22.63/11, 2)

HTH,

Wouter

 

[Zerowaycool]

Where I have a value like activecell.value = some long calculation or even a
sum of figues which end up equalling the answer of 22.63/11 how do I use the
Trunc function ? Do I need to have the activecell.value passed into a
variable and then call the variable into the trunc function. The reason is, I
have a loop running which calculates the activecell and then offsets.

 

[Dave Peterson]

Maybe you could multiply by 100, take the integer part and then divide by 100.

Dim myVal As Double
myVal = 22.63 / 11
MsgBox Int(myVal * 100) / 100

 

[Zerowaycool]

Thanks Dave that will do the job

Maybe you could multiply by 100, take the integer part and then divide by 100.

Dim myVal As Double
myVal = 22.63 / 11
MsgBox Int(myVal * 100) / 100

 

[Dana DeLouis]

Gyus I am just about finished but I can't get Excel to display my currency as
the calculated result. I need to display this accurately so I can take the
figures to my accountant. My issue is that when I calculate the GST on an
amount, the calc is fine but is displayed in correctly eg $22.63 / 11 =
2.05727272727273, so I need it to display it as $2.05 not as it does - $2.06
(I am sure the taxman would like this printout). I looked at the TRUNC
command but can't get it to work.

Is there a way to do this in code to a column of numbers where every row has
been summed(horizontally), or is there another type of display style I should
use.

Thanks in advance

Gav

$22.63 / 11

Hi. Just two cents for consideration.
When one introduces division with currency values, I think one must be
aware of a question. For example: do you want to consider 1.4999999999
as exactly 1.50 ? I think this is important for Rounding Up and Down.
If one considered the above number as 1.50, then there would be no need
to round down to the nearest 2 decimal places.
Again, just something to consider. :>)

Sub Demo()
Dim c As Currency
Dim n, y

'// A division w/ financial numbers
n = 149999999999# / 100000000000#
y = WorksheetFunction.Floor(n, 0.01)
Debug.Print y

'// Rounding w/ Currency
c = 149999999999# / 100000000000#
y = WorksheetFunction.Floor(c, 0.01)
Debug.Print y
End Sub

Returns:
1.49
1.5

= = = = = = =
HTH :>)
Dana DeLouis