Question on MOD

[rich]

Hello,

Running Excel (Office 2007) on WinXP.

My spreadsheet is used for tracking sales of Girl Scout cookies. Need to
determine how many cases and/or boxes of cookies are needed. 12 boxes per
case.

Columns B-I are for each type of cookie.
Rows 4-33 are 1 scout per row.
Row 34 are totals for each type of cookie.
Row 35 has how many cases are needed for each type of cookie.
Row 36 should show how many individual boxes are needed.

Problem:
Row 36 needs to say how many individual boxes are needed.
So, this needs to be the remainder left over after determining the number of
cases.

If I use actual numbers "=MOD(37,3)", result is 1, and is correct.
The formula I'm using is: =MOD (B34,B35)
This always returns a green 0 (zero)
Why? What am I doing wrong?

Thanks,

Rich

 

[Tyro]

What number in B34? What number is in B35?

Tyro

 

[David Biddulph]

And in answering that, make sure you look carefully at what is really in
B35.
Are you perhaps confused by (for example) thinking that you've got 3 but in
fact perhaps you've got a number like =37/3 which is 3.0833333 but only
displayed to zero decimal places?

 

[joeu2004]

The formula I'm using is:  =MOD (B34,B35)
This always returns a green 0 (zero)
Why?  What am I doing wrong?

You are probably confusing the displayed values with the actual values
in B34 and B35. Go to each cell and format them using Scientific with
14 decimal places. (This is just a temporary format. You can use
ctrl-Z to undo it.) You will probably see that what is displayed as
37 or 3 is not exactly that value.

But I suspect that is not the real problem....

Columns B-I are for each type of cookie.
Rows 4-33 are 1 scout per row.
Row 34 are totals for each type of cookie.
Row 35 has how many cases are needed for each type of cookie.
Row 36 should show how many individual boxes are needed.
Problem:
Row 36 needs to say how many individual boxes are needed.
So, this needs to be the remainder left over after determining the
number of cases.

If B35 is "how many cases are needed" of that type of cookie, then
MOD(B34,B35) would not give the number of (additional) individual
boxes required.

I presume that you computed B35 with a formula like =B34/12, where 12
might be the number of boxes per case.

If I'm correct, then first, I would compute B35 with the formula
=INT(B34/12).

And I would compute B36 with the formula =MOD(B34,12), or with the
formula =B34 - 12*B35

PS: Don't forget to order some individual boxes of each cookie to
cover for broken boxes. My wife was GS cookie chair for our service
unit for many years, and we found that there were always broken
boxes.

 

[Tyro]

All the OP has to do is select the cell and look at the value in the formula
bar

Tyro

The formula I'm using is: =MOD (B34,B35)
This always returns a green 0 (zero)
Why? What am I doing wrong?

You are probably confusing the displayed values with the actual values
in B34 and B35. Go to each cell and format them using Scientific with
14 decimal places. (This is just a temporary format. You can use
ctrl-Z to undo it.) You will probably see that what is displayed as
37 or 3 is not exactly that value.

But I suspect that is not the real problem....

Columns B-I are for each type of cookie.
Rows 4-33 are 1 scout per row.
Row 34 are totals for each type of cookie.
Row 35 has how many cases are needed for each type of cookie.
Row 36 should show how many individual boxes are needed.
Problem:
Row 36 needs to say how many individual boxes are needed.
So, this needs to be the remainder left over after determining the
number of cases.

If B35 is "how many cases are needed" of that type of cookie, then
MOD(B34,B35) would not give the number of (additional) individual
boxes required.

I presume that you computed B35 with a formula like =B34/12, where 12
might be the number of boxes per case.

If I'm correct, then first, I would compute B35 with the formula
=INT(B34/12).

And I would compute B36 with the formula =MOD(B34,12), or with the
formula =B34 - 12*B35

PS: Don't forget to order some individual boxes of each cookie to
cover for broken boxes. My wife was GS cookie chair for our service
unit for many years, and we found that there were always broken
boxes.

 

[Tyro]

Assuming, of course, the cells contain numbers and not formulas.