Calculating the difference in quantity from a matrix

[benkennedy26]

Hi Everyone,


Have come here seeking for some advice and will try to explain as best I can.


The scenario is a food company that deliver's a set amount of products dependent on the amount of customers that are booked on a specific day.


So I have a main spreadsheet with a list of food items, for example, bread, milk, orange juice etc. Each Item has its own ratio. For example 1 milk may be for 1-5 people and the orange juice may be 20% of the total customers. This is already mapped out on a spreadsheet and ready to go.


I also have another page where we enter the amount of passengers. This then searches the matrix and lists off the foods and how many are needed relating to the customer number entered.


My problem arises when I would like to show another column of what is increased if the customer number goes up. So if I'm originally told I have 80 customers and get my list, I am later told it is now 90. In my first column i have 80, the next column I now put 10 which updates my food figures. What I would like to add is another column next to my food items that will tell for example that i now need to add another 1 orange juice from looking back at the matrix and calculating the difference between the figures if that makes sense.


for example it currently shows Orange Juice 2


What I would like it show is Orange Juice 3, +1


This would be helpful so we could quickly look down the extra column to just see what the changes are.


I hope one of you has the answer. Many Thanks in advance!!

 

[kweaver]

Is there a chance you would post sample data and the calculations you currently have and what you're looking for in the new calculation(s)?

 

[benkennedy26]

Can I add pictures?

 

[benkennedy26]

https://ibb.co/pbKydwj

 

[Fluff]

You're image & description don't seem to match.
Also your image shows you already have a column showing the info you require, so not sure what your question is.

 

[benkennedy26]

The picture is what I have made to demonstrate. The formula for the old is fine as it generates from the matrix. The formula for new is what i need to calculate for the latter figure whilst not changing the old. Sorry if that still doesnt make sense

 

[Fluff]

Rather than have a column for 80 & then one for 10 (using your original example) make the new column for 90 & then you can simply subtract the old column from the new.