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!

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!

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!

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!

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!