Question on MOD

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rich - 10 Feb 2008 22:13 GMT

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

Reply to this Message

Tyro - 10 Feb 2008 22:24 GMT

What number in B34? What number is in B35?

Tyro

> Hello,
>
[quoted text clipped - 24 lines]
>
> Rich

Reply to this Message

David Biddulph - 10 Feb 2008 22:41 GMT

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?

Signature

David Biddulph

> What number in B34? What number is in B35?
>
[quoted text clipped - 29 lines]
>>
>> Rich

Reply to this Message

joeu2004 - 10 Feb 2008 23:38 GMT

>> 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?

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.
[quoted text clipped - 5 lines]
> 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.

Reply to this Message

Tyro - 10 Feb 2008 23:46 GMT

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

Tyro

On Feb 10, 2:13 pm, rich <r...@discussions.microsoft.com> wrote:

>> 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?

> Columns B-I are for each type of cookie.
> Rows 4-33 are 1 scout per row.
[quoted text clipped - 5 lines]
> So, this needs to be the remainder left over after determining the
> number of cases.

Reply to this Message

Tyro - 10 Feb 2008 23:59 GMT

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

> All the OP has to do is select the cell and look at the value in the
> formula bar
[quoted text clipped - 42 lines]
> unit for many years, and we found that there were always broken
> boxes.

Reply to this Message