Calculate to the third decimal and round up or down in cell

[Roxy]

Hello!
I have a worksheet in MS2003 and users input data into a cell to calculate
information. I need to know if it is possible to write a formula or VB code
to do the following
1. Calculate the number to the third decimal place (1.234)
2. Round up when the last digit is 5 or greater (1.235 is rounded to 1.24)
3. Rounded down when the last number is 4 or less (1.234 is rounded down to
1.23)
The cell is already doing =sum(T3/21.7) to get the calculation I need, now
it just needs to stop at the third decimal and round. Any helpful ideas?
Thanks in advance for all the help!
~Roxy

 

[Dave]

Hi,
Check out the ROUND function in the Help.
=ROUND(sum(T3/21.7),3)
Regards - Dave.

 

[Teethless mama]

=ROUND(sum(T3/21.7),2)

 

[Roxy]

Thank you that was very helpful and informative. But from the Help it didn't
look as though I can do all 3 functions that I need. Am I mistaken?
=ROUND(SUM(T3/21.7),3)
this formula just calculates to the third decimal. It doesn't Round up when
the last digit is 5 or greater or Round down when the last number is 4 or
less, right?
Thanks again you are always so very helpful!

~Roxy

 

[Dave]

As far as I know, the ROUND function does what you want. ie rounds down if
below 5, round up if 5 or above.
ROUNDUP rounds up, regardless and ROUNDDOWN rounds down regardless.
Try putting 1.2225 into A1, the =ROUND(A1,3) into A2.
It should round up to 1.223
Change A1 to 1.2224
It should round down to 1.222
Regards - Dave.

 

[MartinW]

Hi Roxy,

It sounds like you need it to round in 2 steps, first to 3 decimal
places and then to 2, or maybe not, your post is a little unclear.

Using 1.2345 in A1

This formula =ROUND(A1,2)
will return 1.23 (rounds straight to 2 places)

This formula =ROUND(ROUND(A1,3),2)
will return 1.24 (rounds to 3 places then 2)

Pick which result you want and substitute your formula for A1.

There is also no need to use the SUM function, just a simple
T3/21.7 will do.

So I'm guessing your end formula will be
=ROUND(ROUND(T3/21.7,3),2)

HTH
Martin

 

[Spiky]

Thank you that was very helpful and informative. But from the Help it didn't
look as though I can do all 3 functions that I need. Am I mistaken?
=ROUND(SUM(T3/21.7),3)
this formula just calculates to the third decimal. It doesn't Round up when
the last digit is 5 or greater or Round down when the last number is 4 or
less, right?
Thanks again you are always so very helpful!

~Roxy

Let's rephrase something:
Excel ALWAYS calculates to its maximum decimals, which is 15, I think.
ROUND simply cuts it off at whichever point you want, so you want
"=ROUND(SUM(T3/21.7),2)" (Teethless mama's formula). Or possibly use
Martin's, but only if that is really what you want. He gave you
technically what you asked for, but it could give false answers with 2
roundings: For instance 1.2445 ought to be rounded to 1.24 when
showing two places, but if you round first to three places, then to
two you get 1.25. I can't think of a good reason for that to be
correct.

Rounding 5 up and 4 down is the standard in the world, or at least
country, so Excel's basic ROUND function does just that. If you wanted
to do a different form of rounding, THAT would be more complicated.

Another option would be to use =ROUND(SUM(T3/21.7),3) and then format
the cell to 2 decimal places. Just using a specific decimal format
will also show you a rounded (5up, 4down) number. This would give you
the full 3 decimal places, but only show 2 of them (but still have the
potential double-rounding problem I mentioned above). So any further
calculation you do with this number would use the 3 decimals to
calculate.