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?