Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How not to round a formula
I am using SUM to add up a column with numbers that have six decimals. I do
not want the answer to be rounded up. How can I create a formula not to round the answer, using SUM? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How not to round a formula
Just SUM which will not round. If your numbers are exactly 6 decimals, then
display answer with 6 decimals. You could format your cells with 7 decimals (last digit should be 0?) and check SUM this way. "Janet" wrote: I am using SUM to add up a column with numbers that have six decimals. I do not want the answer to be rounded up. How can I create a formula not to round the answer, using SUM? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How not to round a formula
Excel does not automatically round an answer returned by a function, however,
the number of decimals used by the number format will cause a number to display a rounded an answer. Set your numeric format to number with at least 6 decimal positions. Select the range to format and click FORMAT/CELLS on the menu, click the NUMBER tab in the formatting dialog box and select NUMBER from the CATEGORY panel and set the DECIMAL PLACES spinner box to a minimum of 6 decimals -- Kevin Backmann "Janet" wrote: I am using SUM to add up a column with numbers that have six decimals. I do not want the answer to be rounded up. How can I create a formula not to round the answer, using SUM? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How not to round a formula
are you sure the data you are adding has just six decimal significant figures
they may show 6 but actually have more in which case the sum would not necessarily tshow the the total for what is shown. change the nuber of significant figures in your cells you are adding to verify. you may need to round the input cells before adding. "Janet" wrote: I am using SUM to add up a column with numbers that have six decimals. I do not want the answer to be rounded up. How can I create a formula not to round the answer, using SUM? Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How not to round a formula
And if your cell with the =sum() formula is formatted as general, maybe you're
only seeing 2 decimal places. Try widening that column or change to a smaller font. Janet wrote: I am using SUM to add up a column with numbers that have six decimals. I do not want the answer to be rounded up. How can I create a formula not to round the answer, using SUM? Thanks -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How not to round a formula
Wow! Didn't expect such great responses so quickly. In the case of the
formatting, the formula is formatted as a number with 6 decimal places (it has to be). What if part of the formula has a hard number (like 100) and not a cell reference? Would that be apart of the problem? "Dave Peterson" wrote: And if your cell with the =sum() formula is formatted as general, maybe you're only seeing 2 decimal places. Try widening that column or change to a smaller font. Janet wrote: I am using SUM to add up a column with numbers that have six decimals. I do not want the answer to be rounded up. How can I create a formula not to round the answer, using SUM? Thanks -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How not to round a formula
What is the formula you refer to? Having a constant i.e. 100 should make no
difference. It's difficult without having your data available to be more precise in our replies. As already pointed out, what you see as formatted doesn't always represent the internal Excel value 12.3456789 would be seen as 12.345679 to 6 decimal places "Janet" wrote: Wow! Didn't expect such great responses so quickly. In the case of the formatting, the formula is formatted as a number with 6 decimal places (it has to be). What if part of the formula has a hard number (like 100) and not a cell reference? Would that be apart of the problem? "Dave Peterson" wrote: And if your cell with the =sum() formula is formatted as general, maybe you're only seeing 2 decimal places. Try widening that column or change to a smaller font. Janet wrote: I am using SUM to add up a column with numbers that have six decimals. I do not want the answer to be rounded up. How can I create a formula not to round the answer, using SUM? Thanks -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How not to round a formula
can you give an example of what you are doing and show the problem?
if the input data is 6 digits and the finial equatin is six digits it should not have to round up. the the sum of what you see is not the same as what sum() gives, then there is data past the sixth decimal place in your incoming data, even if it shows only 6 "Janet" wrote: Wow! Didn't expect such great responses so quickly. In the case of the formatting, the formula is formatted as a number with 6 decimal places (it has to be). What if part of the formula has a hard number (like 100) and not a cell reference? Would that be apart of the problem? "Dave Peterson" wrote: And if your cell with the =sum() formula is formatted as general, maybe you're only seeing 2 decimal places. Try widening that column or change to a smaller font. Janet wrote: I am using SUM to add up a column with numbers that have six decimals. I do not want the answer to be rounded up. How can I create a formula not to round the answer, using SUM? Thanks -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How not to round a formula
The formula in question is:
=D30-(E11+E23)*D30/100 I checked the D column and E column and both are set with the formatting as Number. "bj" wrote: can you give an example of what you are doing and show the problem? if the input data is 6 digits and the finial equatin is six digits it should not have to round up. the the sum of what you see is not the same as what sum() gives, then there is data past the sixth decimal place in your incoming data, even if it shows only 6 "Janet" wrote: Wow! Didn't expect such great responses so quickly. In the case of the formatting, the formula is formatted as a number with 6 decimal places (it has to be). What if part of the formula has a hard number (like 100) and not a cell reference? Would that be apart of the problem? "Dave Peterson" wrote: And if your cell with the =sum() formula is formatted as general, maybe you're only seeing 2 decimal places. Try widening that column or change to a smaller font. Janet wrote: I am using SUM to add up a column with numbers that have six decimals. I do not want the answer to be rounded up. How can I create a formula not to round the answer, using SUM? Thanks -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How not to round a formula
And what values are in
D30 E11 E23 And if you select the cell with the formula, then hit F2, then F9 and enter, what do you see? (hit edit|Undo to get the formula back) Janet wrote: The formula in question is: =D30-(E11+E23)*D30/100 I checked the D column and E column and both are set with the formatting as Number. "bj" wrote: can you give an example of what you are doing and show the problem? if the input data is 6 digits and the finial equatin is six digits it should not have to round up. the the sum of what you see is not the same as what sum() gives, then there is data past the sixth decimal place in your incoming data, even if it shows only 6 "Janet" wrote: Wow! Didn't expect such great responses so quickly. In the case of the formatting, the formula is formatted as a number with 6 decimal places (it has to be). What if part of the formula has a hard number (like 100) and not a cell reference? Would that be apart of the problem? "Dave Peterson" wrote: And if your cell with the =sum() formula is formatted as general, maybe you're only seeing 2 decimal places. Try widening that column or change to a smaller font. Janet wrote: I am using SUM to add up a column with numbers that have six decimals. I do not want the answer to be rounded up. How can I create a formula not to round the answer, using SUM? Thanks -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Round up Formula Help | Excel Discussion (Misc queries) | |||
How do i round up the sum of a formula | Excel Discussion (Misc queries) | |||
How do I NOT round off in a formula | Excel Worksheet Functions | |||
formula to round up? | Excel Worksheet Functions | |||
How do I ROUND() round off decimals of a column dataset? | Excel Worksheet Functions |