Restaurant Menu Combination calculation

[bertolp]

Hello all,

I am working a sheet and if possible (as I can't think how it would work) I would like to work out the number of menu combination.

3 - Courses (starters - Must have a main courses - Dessert)
2 Courses (Starter - Must have a main courses) or (Must have a main courses - Dessert)
1 Courses (main course only)

I have three column Starters / Mains / Desserts each containing a value (number)

Starters	Mains	Desserts
5	6	4
2	4	1
5	7	2

The first line will then give us 4x 3 courses, 5x 1 courses, 1x 1 courses
The second line will then give us 1x 3 courses, 1x 2 courses, 2x 1 courses
The third line will then give us 2x 3 courses, 3x 2 courses, 2x 1 courses

I hope this is clear enough, and I am looking forward for your replies and hopefully a solution!

Thank you

 

[ranman256]

you need a table for the food:

tato skins, starter
onion rings, starter
chicken, main
steak, main
key lime, dessert
choc. mousse
etc...

a table for the 3 combos
5 starter, cbo1
6 main,cbo1
4 dessert,cbo1
5 , starter, cbo2
4, main, cbo2
etc

then a form to load them into combo/list boxes

choose combo#, which in turn fills the # of allowed choices the diner(/user) can pick to the 'shopping cart'
end of order, adds all values to the 'order' table.

 

[Eric W]

If you have 4 starters, 5 entrees, and 6 desserts, then the number of 3 course meal combinations is 4 * 5 * 6 = 120. The number of starter/entree combinations is 4 * 5 =20. The number of entree/dessert combinations is 5 * 6 =30. The number of entree only combinations is of course 5. So the total number of combinations is 120 + 20 + 30 + 5 = 175.

If you want to enumerate all the combinations, there are formulas and macros to do that, but it's not especially helpful really. Setting up some kind of drop boxes (as ranman256 suggests) with each type in its own box is more useful.

 

[mrshl9898]

D2 =MIN(A2:C2)
E2 =IF(D2=C2,A2-D2,C2-D2)
F2 =B2-SUM(D2:E2)

 

[RicoS]

This is an interesting one!

It's something along the lines of:

X(s+m+d) + Y(s+m) + Z(m+d) + A(m) = 5s + 6m + 4d (taking the first row).

and you need to work out possible values for X,Y,Z and A? There are a few combinations for that first example, so I don't think you could possibly know the "correct" answer, but you could give a few possibilities

For example, X=4, Y=1, Z=0,A=1 would yield the correct result, but so would X=3, Y=1,Z=1,A=0.

Is this what you're looking for? To return all the possibilities? That would be an interesting challenge!

 

[bertolp]

Sorry for the late reply.

Thank you all for your suggestion and for mrshl9898 solution that works like a dream.

Thanks.

View attachment 32784

D2 =MIN(A2:C2)
E2 =IF(D2=C2,A2-D2,C2-D2)
F2 =B2-SUM(D2:E2)