Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display currency as calculated
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display currency as calculated
Hi Gav,
You have to specify the number of digits remaining Try: =Trunc(22.63/11, 2) HTH, Wouter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display currency as calculated
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display currency as calculated
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display currency as calculated
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display currency as calculated
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I display a loss or gain (currency) | Excel Worksheet Functions | |||
change a currency number display | Excel Discussion (Misc queries) | |||
Display Value in 'Currency Format' | Excel Programming | |||
Failure to display currency format | Excel Discussion (Misc queries) | |||
Formatting a text box to display as currency | Excel Worksheet Functions |