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?