How to distribute a number into different categories

Hi everyone
Thanks for reading.

I am making a spreadsheet for a sandwhich catering business.
The user is supposed to put in one number (total sandwiches ordered) and then the spreadsheet automatically works out how many of each type of sandwhich to make.
A few constraints:

- A third of the sandwiches must be vegetarian and distributed evenly amongst the four veg varieties
(I was able to do that fine. It's the next bit that's hard.)

-The remaining 2/3's must be distributed amongst the eight non-veg varieties.
Additionally, some sandwiches are more popular than others, so I have made a column outlining the most popular (from 1st to 3rd) and from that made a popularity % column adding up to 100% that may help.

-Obviously, we can not have a negative number of any type of sandwiches!

I have experimented a lot using rounding up and down and if statements but without much luck. Would be great if we could get the non-veg working and based on the popularity so that we can update this column if popularity shifts.

I've zipped the file and attached it.

Thanks a lot guys. Any help would be appreciated.

Regards,
Andy Englezos

Copy of distribute number.zipHi Englephat

I hope that this responce is not too simplistic for your task. Please see the attached zip file to see the calc.

Hope this helps

Chees
Trilogy

Hi Trilogy, thanks heaps for giving it a go but I'm afraid it still doesn't work.
Can you see that the non-veg number coming up at the bottom doesn't correspond with the non veg total at the top? So unfortunately, this does not solve the problem.

The challenge is that for every 1 sandwich you add to the total only 1 must be distributed.The top non-veg number must be the same as the bottom non-veg number.

Anyways, thanks for the time you put into it

Any other ideas guys?

Andy

Hi Andy

The reason the numbers dont match is that the Non-veg answers are rounded to make it a smooth number. Depending on which one is more popular then the others, you can use ROUNDUP or ROUNDDOWN (within the 1st,2nd and 3rd popularity).

Cheers
Trilogy