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

Hi Gav,

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

HTH,

Wouter

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.


"Wouter HM" wrote:

> Hi Gav,
>
> You have to specify the number of digits remaining
> Try:
> =Trunc(22.63/11, 2)
>
> HTH,
>
> Wouter
>
> .
>

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 wrote:
>
> 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

--

Dave Peterson

Thanks Dave that will do the job

"Dave Peterson" wrote:

> 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 wrote:
> >
> > 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
>
> --
>
> Dave Peterson
> .
>

On 5/7/2010 12:00 PM, Zerowaycool wrote:
> 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