MS excel incorrect autocalc?

[MiniReefKeeper]

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!

 

[Carlos Antenna]

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.

 

[JLatham]

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

 

[Biff]

If you want it rounded to the nearest whole dollar

INT rounds down.

Biff

 

[JLatham]

Thanks for the correction.

INT rounds down.

Biff