Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate to the third decimal and round up or down in cell
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate to the third decimal and round up or down in cell
Hi,
Check out the ROUND function in the Help. =ROUND(sum(T3/21.7),3) Regards - Dave. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate to the third decimal and round up or down in cell
=ROUND(sum(T3/21.7),2)
"Roxy" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate to the third decimal and round up or down in cell
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate to the third decimal and round up or down in cell
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate to the third decimal and round up or down in cell
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 "Roxy" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate to the third decimal and round up or down in cell
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i round decimal if 2.3=2.5 2.7=2.5 & 2.8=3.0 | Excel Worksheet Functions | |||
Round a repeating decimal from a formula... | Excel Worksheet Functions | |||
Round a negative decimal | Excel Worksheet Functions | |||
How do I round last number without a decimal point | Excel Worksheet Functions | |||
ROUND DATA TO 2 DECIMAL PLACES | New Users to Excel |