| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Urgent - Having trouble with decimals
Good day,
This is an urgent request... We are adding fees and taxes, but do not want numbers to round. Here's what we need: SUB-TOTAL $2,963.6300 GST $177.8178 QST $235.6086 TOTAL CA $3,377.0564 We would like the total to show $3,377.05 and not $3,377.06. What formula should we use? By the way, the GST was obtained by multiplying the first total by 0.05, the QST by multiplying GST and first total by 0.075. Any help will be greatly appreciated! Helene |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Urgent - Having trouble with decimals
=ROUNDDOWN(A1,2)
-- Gary's Student gsnu200703 "Helen" wrote: Good day, This is an urgent request... We are adding fees and taxes, but do not want numbers to round. Here's what we need: SUB-TOTAL $2,963.6300 GST $177.8178 QST $235.6086 TOTAL CA $3,377.0564 We would like the total to show $3,377.05 and not $3,377.06. What formula should we use? By the way, the GST was obtained by multiplying the first total by 0.05, the QST by multiplying GST and first total by 0.075. Any help will be greatly appreciated! Helene |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Urgent - Having trouble with decimals
Thank you for responding so fast! Unfortunately, the display I'm getting is
not what I was looking for. Here's my actual data: OUR FEES $2,822.50 ADMINISTRATIVE CHARGES $141.13 SUB-TOTAL $2,963.6300 GST $177.8178 QST $235.6086 TOTAL $3,377.0564 What am I looking for is to get the total to stay at $3,377.05. If I use Rounddown, how do I insert it in my formula? Sorry about insisting... Helene "Helen" wrote: Good day, This is an urgent request... We are adding fees and taxes, but do not want numbers to round. Here's what we need: SUB-TOTAL $2,963.6300 GST $177.8178 QST $235.6086 TOTAL CA $3,377.0564 We would like the total to show $3,377.05 and not $3,377.06. What formula should we use? By the way, the GST was obtained by multiplying the first total by 0.05, the QST by multiplying GST and first total by 0.075. Any help will be greatly appreciated! Helene |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Urgent - Having trouble with decimals
If in A1 thru B5:
OUR 2822.5000 ADMINISTRATIVE 141.1300 SUB-TOTAL 2963.6300 GST 177.8178 QST 235.6086 then in B6: =ROUNDDOWN(SUM(B3:B5),2) -- Gary''s Student gsnu200703 "Helen" wrote: Thank you for responding so fast! Unfortunately, the display I'm getting is not what I was looking for. Here's my actual data: OUR FEES $2,822.50 ADMINISTRATIVE CHARGES $141.13 SUB-TOTAL $2,963.6300 GST $177.8178 QST $235.6086 TOTAL $3,377.0564 What am I looking for is to get the total to stay at $3,377.05. If I use Rounddown, how do I insert it in my formula? Sorry about insisting... Helene "Helen" wrote: Good day, This is an urgent request... We are adding fees and taxes, but do not want numbers to round. Here's what we need: SUB-TOTAL $2,963.6300 GST $177.8178 QST $235.6086 TOTAL CA $3,377.0564 We would like the total to show $3,377.05 and not $3,377.06. What formula should we use? By the way, the GST was obtained by multiplying the first total by 0.05, the QST by multiplying GST and first total by 0.075. Any help will be greatly appreciated! Helene |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Urgent - Having trouble with decimals
You have the Sub-total to 4dp
Are the calculated GST and QST values actualy to 4 decimal places or do these get rounded when actually applied, ie when paid ? If these are rounded to 2dp's when paid, they should be rounded here in excel as well - before producing the total Anyway same function =rounddown(cell,2) Steve On Thu, 01 Feb 2007 20:19:01 -0000, Helen wrote: Thank you for responding so fast! Unfortunately, the display I'm getting is not what I was looking for. Here's my actual data: OUR FEES $2,822.50 ADMINISTRATIVE CHARGES $141.13 SUB-TOTAL $2,963.6300 GST $177.8178 QST $235.6086 TOTAL $3,377.0564 What am I looking for is to get the total to stay at $3,377.05. If I use Rounddown, how do I insert it in my formula? Sorry about insisting... Helene "Helen" wrote: Good day, This is an urgent request... We are adding fees and taxes, but do not want numbers to round. Here's what we need: SUB-TOTAL $2,963.6300 GST $177.8178 QST $235.6086 TOTAL CA $3,377.0564 We would like the total to show $3,377.05 and not $3,377.06. What formula should we use? By the way, the GST was obtained by multiplying the first total by 0.05, the QST by multiplying GST and first total by 0.075. Any help will be greatly appreciated! Helene -- Steve (3) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Urgent - Having trouble with decimals
Yesssss!!! Thank you!
"Gary''s Student" wrote: If in A1 thru B5: OUR 2822.5000 ADMINISTRATIVE 141.1300 SUB-TOTAL 2963.6300 GST 177.8178 QST 235.6086 then in B6: =ROUNDDOWN(SUM(B3:B5),2) -- Gary''s Student gsnu200703 "Helen" wrote: Thank you for responding so fast! Unfortunately, the display I'm getting is not what I was looking for. Here's my actual data: OUR FEES $2,822.50 ADMINISTRATIVE CHARGES $141.13 SUB-TOTAL $2,963.6300 GST $177.8178 QST $235.6086 TOTAL $3,377.0564 What am I looking for is to get the total to stay at $3,377.05. If I use Rounddown, how do I insert it in my formula? Sorry about insisting... Helene "Helen" wrote: Good day, This is an urgent request... We are adding fees and taxes, but do not want numbers to round. Here's what we need: SUB-TOTAL $2,963.6300 GST $177.8178 QST $235.6086 TOTAL CA $3,377.0564 We would like the total to show $3,377.05 and not $3,377.06. What formula should we use? By the way, the GST was obtained by multiplying the first total by 0.05, the QST by multiplying GST and first total by 0.075. Any help will be greatly appreciated! Helene |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Urgent - Having trouble with decimals
You are very welcome!
-- Gary's Student gsnu200703 "Helen" wrote: Yesssss!!! Thank you! "Gary''s Student" wrote: If in A1 thru B5: OUR 2822.5000 ADMINISTRATIVE 141.1300 SUB-TOTAL 2963.6300 GST 177.8178 QST 235.6086 then in B6: =ROUNDDOWN(SUM(B3:B5),2) -- Gary''s Student gsnu200703 "Helen" wrote: Thank you for responding so fast! Unfortunately, the display I'm getting is not what I was looking for. Here's my actual data: OUR FEES $2,822.50 ADMINISTRATIVE CHARGES $141.13 SUB-TOTAL $2,963.6300 GST $177.8178 QST $235.6086 TOTAL $3,377.0564 What am I looking for is to get the total to stay at $3,377.05. If I use Rounddown, how do I insert it in my formula? Sorry about insisting... Helene "Helen" wrote: Good day, This is an urgent request... We are adding fees and taxes, but do not want numbers to round. Here's what we need: SUB-TOTAL $2,963.6300 GST $177.8178 QST $235.6086 TOTAL CA $3,377.0564 We would like the total to show $3,377.05 and not $3,377.06. What formula should we use? By the way, the GST was obtained by multiplying the first total by 0.05, the QST by multiplying GST and first total by 0.075. Any help will be greatly appreciated! Helene |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Urgent - Having trouble with decimals
Why not use INCREASE DECIMAL. It works in my spread sheet? You can increase
the decimal number to what you want - there's no practical limit. (10-10,000 decimal places.) "Helen" wrote: Good day, This is an urgent request... We are adding fees and taxes, but do not want numbers to round. Here's what we need: SUB-TOTAL $2,963.6300 GST $177.8178 QST $235.6086 TOTAL CA $3,377.0564 We would like the total to show $3,377.05 and not $3,377.06. What formula should we use? By the way, the GST was obtained by multiplying the first total by 0.05, the QST by multiplying GST and first total by 0.075. Any help will be greatly appreciated! Helene |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Urgent - Having trouble with decimals
Because changing the display doesn't change the value XL uses to
calculate follow-on calculations (unless the Precision as displayed option is set). And of course there's a practical limit ot using INCREASE DECIMAL: XL only carries 15 decimal digits of precision, so for a number =1, if you have 10,000 decimal places, the last 9,986 will all be zeros. In article , Danny C wrote: Why not use INCREASE DECIMAL. It works in my spread sheet? You can increase the decimal number to what you want - there's no practical limit. (10-10,000 decimal places.) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Displaying a number with different decimals depending on a condition | Excel Worksheet Functions | |||
| Conditional formatting : amount of decimals | Excel Discussion (Misc queries) | |||
| How do I change the number format for decimals from , to .? | Setting up and Configuration of Excel | |||
| How do I calculate without decimals in excel? | Excel Worksheet Functions | |||
| Urgent Urgent Urgent!!! | Excel Discussion (Misc queries) | |||
Linear Mode
