I'm trying to calculate if an item will still be in stock when used in a certain amount during the week or if I need to buy them and by how much.

Current I have this set up:

A1=18
A2=5



A1 is 18 eggs per box and A2 is the quantity used. My formula is:

=IF(A1-A2*7<=5,"BUY","IN STOCK")

The 7 is from 7 days/week. I want the "BUY" part to look something like: "BUY=1" if it's lower than A2.

Thanks if you can help.

Nobody, I thought I understood this but as I got into the details I decided I need to ask questions first. What would make sense to me is if you have, or want, a worksheet like this:With this arrangement (which is what I suspect you're building toward though you haven't spelled it out), you can put in column D how much you figure you sell every day. Column E has a formula "=Dn*8" which calculates how much you should have in stock so as to have one day's stock left over by reorder time next week. (You don't want to have five left over for every item; you want to have more left over of the items that sell faster, right?)

If so, then the formula for column C would look like this:But I made a lot of guesses while I was trying to fill in the blanks in my understanding; I may have filled them in completely wrong.

BobBridges, I confused myself too, haha. Let's make your description measurements smaller, so I don't confuse you or me. Let's say currently in stock:

Butter has 28 tbsp/box. If 1 box=32 tbsp.
Milk has 23 .5 cups/gallon. If 1 gallon=32 .5 cups.
Eggs has 12 eggs/carton. If 1 carton=18 eggs.

In your example, I will need an to see how many cartons of eggs I need to buy. If calculated correctly, I will need to buy 2 cartons of eggs for the week if I use 5/day with a current stock of 12 eggs. So the cell should say BUY=2.

I'm only calculating from a full box, gallon, carton, etc only. So when calculated in the beginning, I wouldn't have an "in stock" column, but a current full box column.

Hopefully that clears things up.

Edit: I think I got it to work

=IF(Bn<=En*7,"BUY="&ROUNDUP(SUM(SUM(En*7)-Bn)/En,0)),"IN STOCK"))

If that works for you, ok (I have a ton of stuff to do today so I won't try too hard to check), but you don't need the SUM functions there. The same thing happens without them:No, wait, there's a problem here. In column E, I have eight days' supply already. The whole point of doing that is not to have to repeat the multiplication in the IF function. If you don't want to have the extra column E, then you multiply by D instead.

Thank you Bob, I have removed my SUM functions.

You are correct, but you had the example up so I used it that way. In my workbook, I actually don't have an E column. I used D instead as you stated.