Urgent - Having trouble with decimals

[Helen]

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

 

[Gary''s Student]

=ROUNDDOWN(A1,2)

 

[Helen]

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

 

[Gary''s Student]

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)

 

[SteveW]

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

 

[Helen]

Yesssss!!! Thank you!

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]

You are very welcome!

 

[Danny C]

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.)

 

[JE McGimpsey]

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.