Hi
If doing this on a computer it is relatively simple using a 'bunch' of IF statements, but as for a formula
Hello, this is a bit of an interesting problem that I can't get my head around. To help explain the problem, let's compare it to Netflix's service.
Let's say you belong to Mathflix, a monthly subscription service that rents math books. Say you have a plan that allows you to read two books at home at any given time, and to read a maximum of 4 books per month.
Every day a computer runs through the membership tables at Mathflix and tries to decide if any members should be shipped new books.
Is there a formula that can always calculate the correct number of books a person should receive assuming the following constants?
Books-Limit-Per-Month = 4
Books-Limit-At-Home = 2
And the following variables:
Current-Books-At-Home
Current-Books-Queued-For-Shipping
Current-Books-Received-This-Month
I've tried all sorts of combinations and failed. The problem seems a lot simpler than it actually is. I would appreciate any input.
Many thanks
If I'm understanding correctly,
Let H = "Current-Books-At-Home" (0 <= H <= 2)
Let Q = "Current-Books-Queued-For-Shipping" (0 <= Q)
Let R = "Current-Books-Received-This-Month" (0 <= R <= 4)
Then I believe mathflix should ship min(4-R,2-H,Q) books to that person.
But it's not a combinations/permutations question, so maybe I misunderstood what you want?
Thanks, undefined. You are right, I phrased the question incorrectly. In fact, I butchered the question entirely. What I would like to determine is "Current-Books-Queued-For-Shipping," or more precisely, how many books should we queue for shipping; how many books to queue for shipping will of course be influenced by the constants I provided, and the variables current-books-at-home and current-books-received-this-month.
I don't think the min() function would work as you provided for that scenario... sorry for not being clear enough earlier on.
I'm afraid I don't know what it is we're trying to count.
1) Is this for an individual customer, or all customers at once?
2) Is there a limit to how many books are in the entire inventory of mathflix?
3) What if a customer wants a book that is not in inventory? Are we concerned about this?
4) Do customers have to use all 4 books per month?
Maybe you could give a very small example involving maybe two or three customers to make clear what it is we're counting?
Let's assume the followng:
- It's for one customer.
- Inventory is not an issue.
- Plan includes a maximum of two books at home and a maximum of 4 books per month; the customer is under no obligation to do anything other than stay under his or her limit.
- books-at-home-limit = bh
- books-per-month-limit = bm
- current-books-at-home = ch
- current-books-this-month = cm
- queued-for-shipping = qs
Examples:
Customer A joins Mathflix. The computer want's to know how many books it should to Customer A. In this case it would be:
min(bm - cm, bh - bm)
The problem is when we introduce another variable into the mix: queued-for-shipping. You have to account for anything that's marked as "queued for shipping" in the forumula, and I suspect I might have just arrived at the answer:
min(bm - (cm + qs), bh - (bm + qs))
I think that sounds right, no?
Won't this always evaluate to -2 for the conditions given? Because bh = 2 and bm = 4 so bh - bm = -2.
So the original way I interpreted the queued list is: a list of books that the customer wants and are in inventory. So if 7 books are queued, then no more than 7 books can be sent; likewise if no books are queued, then no books can be sent. Did I misinterpret?
For an example I was thinking more along the lines of, a customer Jim joins mathflix so initially ch=0, cm=0, bh=2, bm=4, and qs=0. (All the following takes place in the same month.) He decides he wants 5 books and they are available so now qs=5. The computer then determines that he should get 2 books (because it should send as many as possible without surpassing the limits -- and it can't send more than 2 because bh=2). Some days later, Jim is done with 1 book and returns it. So the computer then sees that there are 3 books in the queue, 1 book at home, and 2 books used for this month, so it is okay to send another book. So after this transaction ch=2, cm=3, bh=2, bh=4, and qs=2. Then the customer returns both books. So then the computer sends one book (it can't send more because of the monthly limit). Then the customer returns that book. Then the computer sees that the limit is reached and can't do anything until next month. So at the end, ch=0, cm=4, bh=2, bh=4, qs=1.
If this is how it plays out then I don't see why my original formula doesn't hold.. But if this isn't what you mean please clarify.
Ahh, I see where the misunderstanding is. What I mean by queuedForShipment is that a book is marked to be shipped, not that it's part of a member's list, which is entirely different.
Your approach makes queuedForShipment redundant, as it should it be assuming that this is a real world scenario and you wanted to use database polling to determine what to ship.
In an event driven system, however, queuedForShipping would be required since certain events will need to change queuedForShipping from 0 to 1. This way shipping is can be determined on the fly.
Still not completely clear.. but how close do you feel you are to an answer? If you have a formula you think is right, and if you test it with a bunch of small data and it gives the expected the results, then there's a decent chance you got it right.
If you need more help, maybe you could clarify about queuedForShipping -- does a book get marked 1 when it's packaged up without an address label, or only after it gets an address label? Also, are we trying to solve for queuedForShipping (the count, not the boolean flag) or are we trying to solve for some other (unnamed) variable?
I suspect that you meant min(bm-cm, bh-ch) instead of min(bm - cm, bh - bm) in post #6. Also I'm still wondering whether min(bm-cm, bh-ch, qs) would make sense in your case.