0

Context: Trying to figure out which price increases of our menu makes the most sense. Our guests drink for an average amount of 12 euro's every night. We have a set menu of prices for each drink we offer at our bar.

Drinks Price
Beer 1,50
Mix 3,50
Soda 0,50
Wine 2,00
Shots 3,00

Now I would like to generate all combinations that are possible with this menu for the 12 euro people spend on average. Is this possible in Google sheets and how would I go about this?

When I have generated the combinations I would like to see what a price increase does to the total spending for a person on a night and to see what it would do with our income.

We are a bar which is run by volunteers and all the money that is spend at ours we use to buy for the next night and the rest goes to our fund where we support projects that need our financial help.

Dorien
  • 13
  • 4
  • https://stackoverflow.com/a/68775825/5632629 – player0 Oct 13 '21 at 09:31
  • But that does not take into account the max value I want to work with? Cause i would want a list of 3 mix drinks, 8 beers, 24 soda, 2 mix drinks 3 beers etc etc – Dorien Oct 13 '21 at 10:17

1 Answers1

0

I think you can do it with mixed radix as follows:

=ArrayFormula(filter(mod(quotient(sequence(A4,1,0),B4:F4),A3:E3),
mmult(mod(quotient(sequence(A4,1,0),B4:F4),A3:E3),transpose(A2:E2))<=G2))

enter image description here

The first few rows on the left contain the drinks, their prices, max number of each type of drink that can be purchased for $12 (plus one where this is an exact amount), and the cumulative products of the previous row from right to left.

With a 1 in F3 and F4, the formula in A3 is

=quotient($G$2,A2)+(mod($G$2,A2)=0)

and the formula in A4 is

=product(A3:$F3)

pulled across.

A lower limit can be put on it as well:

=ArrayFormula(filter(mod(rounddown(sequence(A4,1,0)/B4:F4),A3:E3),
(mmult(mod(rounddown(sequence(A4,1,0)/B4:F4),A3:E3),transpose(A2:E2))<=G2)*
(mmult(mod(rounddown(sequence(A4,1,0)/B4:F4),A3:E3),transpose(A2:E2))>G4)))

where G4 contains the minimum value.

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37