1

I have menu items that contain the same inventory ingredients and want to able to deplete that inventory ingredient every time I sell menu items:

For example:

  • If I have 2 pounds (32 oz.) of meat as an inventory ingredient in cell B2
  • I sell one burrito that contains 8 oz. in cell D2
  • I sell one taco that contains 4 oz. in cell D3
  • I sell one torta that also contains 4 oz. in cell D4

I want to able to input data in cells D2, D3 and D4 and see the balance result in real time:

        A.      B.          C.        D
1.     INGREDIENTS.         MENU ITEMS
2      meat.   32           Burrito.  1
3.                          Taco      1
4.                          torta.    1

Note that B2 contains 32 oz total of ingredient and I'm selling 3 menu items D2, D3, D4. The burrito contains 8 oz, the taco 4 oz and the torta 4 oz, totaling 16 oz.

I want to make cell B2 subtract the 16 oz from the sale of the three menu items and show that I have 16 oz remaining.

In general I need to know if you can keep a running balance in one cell.

  • 1
    Please post some sample data and desired output, that would help us to understand your problem. – Máté Juhász Nov 20 '16 at 20:11
  • 1
    You might have to rework it a little, you could put the initial quantity of meat in A1, then have a field for current quantity in B1, and put your sales items in C1,C2 etc. so B1 will be =A1 - SUM(C:C) You could add some checks for when the value becomes less than 0, so the cell becomes red in color or something like that – Anoop Ramakrishna Nov 20 '16 at 20:14
  • @AnoopRamakrishna, this is real time, so conditional formatting probably isn't necessary. When the last ordered item uses up the end of the meat, it will be evident that no more tacos can be made. :-) How about converting your comment to an answer? – fixer1234 Nov 20 '16 at 21:33
1

@Anoop Ramakrishna has the right idea, but hasn’t completed it.  A simple formula (which you could put into cell B3, for example) is

=B2 - 8*D2 - 4*D3 - 4*D4

because D2 is the number of burritos, and you use 8 oz. per burrito, etc.

        

This could become cumbersome as your menu expands, or if you ever change your recipes.  A more scalable solution is to put your recipe numbers into the spreadsheet (e.g., in Column E) and then use the

=B2 - SUMPRODUCT(D2:D9, E2:E9)

formula.

        

This is equivalent to the first formula, except

  • The 8, 4, and 4 are in the spreadsheet, so they’re easier to change.
  • You don’t have to list all the terms.  The (second) formula, as written above, allows you to add five more menu items (down to Row 9) without changing the formula (because it looks at all the numbers in the ranges D2 through D9 and E2 through E9).  If you need more than that, just change the 9s to the last row number you need to include.
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.