Need help with a (probably) easy math equation (using Excel)

Factual Questions
1

I cannot help but suspect the following has a simple solution I should know but for the life of me I can’t figure it out.

Say I have cases of items that come 6/case. When sold as a case they cost $9 ($1.50/unit). When I sell them individually they go for $2/unit (or $12 for the whole case without discount).

Now, assume someone wants to buy 87 units. That is 14 cases (84 units) at $1.50 each and 3 units left over at $2 each.

While not difficult to just wangle the numbers I need to put it into a spreadsheet so those even less mathematically inclined than I am (scary huh?) can just type in the unit amount and get the answer handed to them.

Any ideas how best to go about this in Excel?

2

Try (using the numbers for your examples), A=87 mod 6 (I think thats the command for modular division in Excel but I don’t have the program here to check, if it doesn’t work just search for “modular” in the help file).

A will then be the number of individual units you want. Then B=(87-A)/6 gives the number of cases.

ETA: actually, googled it. Command should be A=Mod(87,6)

3

The above works, or you can do TRUNC(87/6) to get the number of cases and MOD(87,6) to get the number of individual items.

4

Fast answers! Thanks you two!

5

20 minutes for the absolute answer to a question on the SDMB - is that a record?

6

I can’t give you a cite but that is hardly the record.