Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MS excel incorrect autocalc?
Im working on a spreadsheet where I want Column E to be 1/5th of the dollar
value in Column D. So far Ive had to enter =MOD(Dx,5) for each D cell (ie x) value. This works for some of the values, yet others auto-calculate wrong. I.e. when I typed in $15.31 in cell D17, it showed $0.31 in E17; and $25.00 auto-calculated as $-. Yet it seems to work correctly for 12.41 and 12.50 values. The cells have been formatted to currency. Can anybody tell me what Im doing wrong and how to correct this problem? Is there a way to format the entire column to reflect 1/5 of the previous columns value so I dont have to retype the formula in every column E cell? A BIG thanks to anyone who can help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MS excel incorrect autocalc?
The MOD function returns the modulus or remainder after a division
operation. In this case =MOD(25,5) would correctly evaluate to zero. If I understand correctly what you are trying to do, the formula you are looking for is simply =Dx/5. You can enter the formula in one cell and then select the cell where you have entered the formula. In the lower right corner of the cell there is a small square, this is the fill handle. Click and drag the fill handle down the column as far as you wish. -- Carlos "MiniReefKeeper" wrote in message ... I'm working on a spreadsheet where I want Column E to be 1/5th of the dollar value in Column D. So far I've had to enter =MOD(Dx,5) for each D cell (ie "x") value. This works for some of the values, yet others auto-calculate wrong. I.e. when I typed in $15.31 in cell D17, it showed $0.31 in E17; and $25.00 auto-calculated as $-. Yet it seems to work correctly for 12.41 and 12.50 values. The cells have been formatted to "currency". Can anybody tell me what I'm doing wrong and how to correct this problem? Is there a way to format the entire column to reflect 1/5 of the previous' column's value so I don't have to retype the formula in every column E cell? A BIG thanks to anyone who can help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MS excel incorrect autocalc?
Carlos has given good explanation of MOD() function along with the general
formula to use if you want to divide the entire amount in Dx by 5. If you want JUST the dollar amount divided by 5 use this instead: =TRUNC(Dx)/5 TRUNC() simply removes the decimal portion without rounding. If you want it rounded to the nearest whole dollar before dividing use: =INT(Dx)/5 "Carlos Antenna" wrote: The MOD function returns the modulus or remainder after a division operation. In this case =MOD(25,5) would correctly evaluate to zero. If I understand correctly what you are trying to do, the formula you are looking for is simply =Dx/5. You can enter the formula in one cell and then select the cell where you have entered the formula. In the lower right corner of the cell there is a small square, this is the fill handle. Click and drag the fill handle down the column as far as you wish. -- Carlos "MiniReefKeeper" wrote in message ... I'm working on a spreadsheet where I want Column E to be 1/5th of the dollar value in Column D. So far I've had to enter =MOD(Dx,5) for each D cell (ie "x") value. This works for some of the values, yet others auto-calculate wrong. I.e. when I typed in $15.31 in cell D17, it showed $0.31 in E17; and $25.00 auto-calculated as $-. Yet it seems to work correctly for 12.41 and 12.50 values. The cells have been formatted to "currency". Can anybody tell me what I'm doing wrong and how to correct this problem? Is there a way to format the entire column to reflect 1/5 of the previous' column's value so I don't have to retype the formula in every column E cell? A BIG thanks to anyone who can help! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MS excel incorrect autocalc?
If you want it rounded to the nearest whole dollar
INT rounds down. Biff "JLatham" wrote in message ... Carlos has given good explanation of MOD() function along with the general formula to use if you want to divide the entire amount in Dx by 5. If you want JUST the dollar amount divided by 5 use this instead: =TRUNC(Dx)/5 TRUNC() simply removes the decimal portion without rounding. If you want it rounded to the nearest whole dollar before dividing use: =INT(Dx)/5 "Carlos Antenna" wrote: The MOD function returns the modulus or remainder after a division operation. In this case =MOD(25,5) would correctly evaluate to zero. If I understand correctly what you are trying to do, the formula you are looking for is simply =Dx/5. You can enter the formula in one cell and then select the cell where you have entered the formula. In the lower right corner of the cell there is a small square, this is the fill handle. Click and drag the fill handle down the column as far as you wish. -- Carlos "MiniReefKeeper" wrote in message ... I'm working on a spreadsheet where I want Column E to be 1/5th of the dollar value in Column D. So far I've had to enter =MOD(Dx,5) for each D cell (ie "x") value. This works for some of the values, yet others auto-calculate wrong. I.e. when I typed in $15.31 in cell D17, it showed $0.31 in E17; and $25.00 auto-calculated as $-. Yet it seems to work correctly for 12.41 and 12.50 values. The cells have been formatted to "currency". Can anybody tell me what I'm doing wrong and how to correct this problem? Is there a way to format the entire column to reflect 1/5 of the previous' column's value so I don't have to retype the formula in every column E cell? A BIG thanks to anyone who can help! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MS excel incorrect autocalc?
Thanks for the correction.
"Biff" wrote: If you want it rounded to the nearest whole dollar INT rounds down. Biff "JLatham" wrote in message ... Carlos has given good explanation of MOD() function along with the general formula to use if you want to divide the entire amount in Dx by 5. If you want JUST the dollar amount divided by 5 use this instead: =TRUNC(Dx)/5 TRUNC() simply removes the decimal portion without rounding. If you want it rounded to the nearest whole dollar before dividing use: =INT(Dx)/5 "Carlos Antenna" wrote: The MOD function returns the modulus or remainder after a division operation. In this case =MOD(25,5) would correctly evaluate to zero. If I understand correctly what you are trying to do, the formula you are looking for is simply =Dx/5. You can enter the formula in one cell and then select the cell where you have entered the formula. In the lower right corner of the cell there is a small square, this is the fill handle. Click and drag the fill handle down the column as far as you wish. -- Carlos "MiniReefKeeper" wrote in message ... I'm working on a spreadsheet where I want Column E to be 1/5th of the dollar value in Column D. So far I've had to enter =MOD(Dx,5) for each D cell (ie "x") value. This works for some of the values, yet others auto-calculate wrong. I.e. when I typed in $15.31 in cell D17, it showed $0.31 in E17; and $25.00 auto-calculated as $-. Yet it seems to work correctly for 12.41 and 12.50 values. The cells have been formatted to "currency". Can anybody tell me what I'm doing wrong and how to correct this problem? Is there a way to format the entire column to reflect 1/5 of the previous' column's value so I don't have to retype the formula in every column E cell? A BIG thanks to anyone who can help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need suggestions for some uses of Ms Excel | Excel Discussion (Misc queries) | |||
How can I locate links in an excel file and fix if incorrect? | Excel Discussion (Misc queries) | |||
Value Errors with EXCEL XP not showing up in EXCEL 2000 | Links and Linking in Excel | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Excel Calculation is Incorrect | Excel Worksheet Functions |