1

You need 100 lbs of bird feed. John's bag can carry 15 lbs and Mark's bag can carry 25 lbs. Both guys have to contribute exactly the same total amount each. What's the lowest number of trips each will have to take?

I have calculated this using systems of equations.

15x + 25y = 100 15x - 25y = 0

This equals out to: John would have 3.33 trips and Mark would have 2 trips. Only one problem: you can't have 1/3 of a trip.

The correct answers is: John would take 5 trips (75 lbs) and Mark would take 3 trips (75 lbs).

How do you calculate this? Is there an excel formula which can do both layers of this?

oneintheword
  • 155
  • 1
  • 7
  • Excel has a LCM (Least Common Multiple) function. So the answer would be `LCM(15, 25) / 15` and `LCM(15, 25) / 25` – heijp06 Aug 31 '14 at 06:50
  • Are you sure that isn't just coincidental? Did you run some tests with other values? What if e.g. John's bag limit was 10 and Mark's 4? Your formulas would give LCM(10,4)/10 (=2) for John and LCM(10,4)/4 (=5) for Mark, though clearly their combined total (2*10+5*4 (=40)) would not be sufficient. – XOR LX Aug 31 '14 at 10:10
  • @XORLX: You are right, I did not take into account the requirement of having at least 100 lbs of birds feed, to get that you need to multiply the values I calculate with `CEILING(100 / (2 * LCM(15; 25)); 1)`. I have added that as answer. – heijp06 Aug 31 '14 at 10:43
  • Hmmm. Still not sure. Again, although that works for those values (15 and 25), not sure it works in all cases. Did you run more checks this time? What about with those values I gave above? – XOR LX Aug 31 '14 at 10:47
  • @XORLX: With the values you gave I get 6 trips for John and 15 trips for Mark. They then both carry 60 lbs for a total of 120. I have added an explanantion to my answer to carify why I think it is correct. – heijp06 Aug 31 '14 at 10:52
  • @Peter: Sincere apologies. I miscopied your formulas. Your solution does indeed work and, what's more, is a lot simpler and better than mine. Regards. – XOR LX Aug 31 '14 at 11:10
  • @XORLX: Thank you :-). BTW, I was not offended by your comments in any way. – heijp06 Aug 31 '14 at 11:13
  • @Peter: I'm glad! :-) And well done again - fine logic! Regards. – XOR LX Aug 31 '14 at 11:17
  • @PetervanderHeijden Thank you! Great work. – oneintheword Aug 31 '14 at 14:56

2 Answers2

1

Assuming you put the total bird feed required in A1 and John's and Mark's bag limits in B1 and B2 respectively, then this formula in C1:

=MATCH(TRUE,INDEX(2*ROW(INDIRECT("1:100"))*LCM($B$1:$B$2)>=$A$1,,),0)*LCM($B$1:$B$2)/B1

will give the lowest number of trips required of John. Copying this formula down to C2 will give the equivalent result for Mark.

Note that the 100 in the part:

ROW(INDIRECT("1:100"))

was arbitrarily chosen and will give correct results providing neither John nor Mark is required to make more than twice that number of trips, i.e. 200. Obviously you can amend this value if you feel it necessary (up to a theoretical limit of 2^20).

Regards

XOR LX
  • 7,632
  • 1
  • 16
  • 15
1
  • Since John and Mark need to carry the same total amount of bird feed, what they will carry has to be a multiple of the least common multiple.
  • Since they both carry that amount the total amount will always be an even multiple of the LCM.
  • So find the least even multiple of the LCM that is larger than 100. And calculate the number of trips John and Mark will have to take from that.

For John:

CEILING(100/(2*LCM(15; 25));1)*LCM(15;25)/15

For Mark:

CEILING(100/(2*LCM(15; 25));1)*LCM(15;25)/25
heijp06
  • 11,558
  • 1
  • 40
  • 60
  • 1
    You can simplify a little so that LCM is only used once, i.e. for John `=CEILING(100/2,LCM(15,25))/15` – barry houdini Aug 31 '14 at 18:45
  • @barryhoudini: Nice usage of the `significance` parameter of the `CEILING` function. That is much more elegant than what I have. – heijp06 Aug 31 '14 at 19:54