Hello Folks
I have an Excel workbook with 2 sheets
Sheet1 (prod-sales-history) has a list of few items and a monthly sales number for 2103
Sheet2 (inventory) illustrates the inventory status of these items during the months of 2013
I am trying to write a formula in Sheet1 monthly columns when they are null or they have 0
If null or 0 then check the inventory sheet for the same item for that period
- if the sheet2 column for the same item > 0 then I know this item had stock in that period so I will mark Sheet1.Column = 0
- otherwise if the sheet2 column for the same item is 'NULL' then I will take the average sales for the full year and assign the value in Sheet1.Column
I am unable to figure how can I go about doing this. Please can someone advise?
I probably have not explained it well but I hope someone may understand.
Thanks
John
stock-zero-coz-oos-or-new.xlsx
Do you have a separate reference set for yearly sales or do you expect to overtype the formula with a number?
Excel cannot have either a value or a formula for a particular cell it must have one or the other so the formula should (I think!):-
Look for the monthly sales for an item (from where?)
if that is null or 0 check the inventory sheet and put a 0 if there was stock and the average monthly sales (from where??) if not
Is that correct? - If so, are the figures currently quoted the monthly sales figures?