#1
|
|||
|
|||
Adding quantity if statement
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. |
#2
|
||||
|
||||
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:
Code:
A B C D E 1 Description In stock Status Used/day 8 days 2 Butter, lb 28 IN STOCK 3 24 3 Milk, gallon 23 BUY=25 6 48 4 Eggs, 18/carton 12 BUY=28 5 40 5 Sour cream, lb 13 IN STOCK 1 8 If so, then the formula for column C would look like this: Code:
=IF(Bn<En,"BUY="&En-Bn,"IN STOCK") |
#3
|
|||
|
|||
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")) Last edited by ImAhNoBoDy; 06-23-2014 at 05:04 AM. |
#4
|
||||
|
||||
Quote:
Code:
=IF(Bn<=En*7,"BUY="&ROUNDUP((En*7-Bn)/En,0)),"IN STOCK")) |
#5
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How can I print a different quantity of each page? A macro maybe? | unittwentyfive | Word | 2 | 04-05-2014 07:47 PM |
Add a statement | sma | Word | 6 | 03-23-2014 10:30 AM |
if statement | piper7971 | PowerPoint | 1 | 08-19-2010 07:10 AM |
automatic quantity updation of total amount is restricted. | aligahk06 | Excel | 0 | 05-23-2010 08:39 AM |
Help with IF statement! | CPelkey | Word | 1 | 04-12-2010 09:06 AM |