calculations in MS excel

[MiniReefKeeper]

I think my first post failed; so i'm re-posting...sorry for cross posings...

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!

 

[Ken Johnson]

Hi MiniReefKeeper,

You are using the wrong formula.

MOD(15.31,5) = 0.31 is correct for that formula. The MOD formula gives
the REMAINDER after 15.31 is divided by 5. 15.31/5 = 3 remainder 0.31

To get one fifth of each column D value into the corresponding column E
cell..

1. Type this into E2....

=D2/5

2. Now, when you move the cursor (fat cross) so that it is positioned
directly over cell E2's bottom right-hand corner you should see it turn
into a skinny cross. When you see the cursor change double-click and
you should see the formula automatically fill column E with the correct
formula down to the row with the last column D value.

Ken Johnson

 

[JLatham]

His earlier post seems to have shown up with title of "MS excel incorrect
autocalc?" and has a couple of other replies in it also.

 

[MiniReefKeeper]

Thanks Ken! You just made my life alot easier! Cheers.

 

[Ken Johnson]

Hi MiniReefKeeper,

You're welcome, thanks for the feedback.

Ken Johnson