Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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

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?

Thank you for picking this up for me.

>> Do you have a separate reference set for yearly sales or do you expect to overtype the formula with a number?

I can prepare a separate refrence but then I thought instead of creating another sheet it would be great if I can onvertype the formula in the same cells (sheet1)

>>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!):-

if Sheet1.Cell is empty or 0 then I want to compare the data with Sheet2.cell for the same period

- if null, then may the item does not exist in the sheet2

- if 0, then may be there were sales in that period of sheet2

- if 0, then may be item was out of stock in that period of sheet2

I am trying to figure why the data in Sheet1 has null or zero, based on the sheets2 data.

I hope this makes sense?

Thanks

Frank's sheet gives you a start point but assumes that inventory contains average sales which doesn't seem right; I think you should consider structuring your data around his suggestion though:-

sheet1 - Prod Sales History

Sheet2 - Inventory

Sheet3 - Report

The advantage of this is that you can re-use the sheets when you get sales information about various products or new months. If you overtype the formula you won't be able to restart as easily

If I want to highlight the changes in RED just to alert the changes ... please can you advise how can I go about do this in the formula below?

=IF('prod-sales-history'!R

ie: SetRedColor(IF(inventory!R

Many thanks

R1C1 means the cell in Row 1 (R1) Column 1 (C1), absolute, aka A1

RC1 means the cel in the same row (relative), column 1 (absolute)

R1C means the cell in Row 1 (absolute), same column (relative)

R(-1)C(-1) means the cell relatively 1 row up, one column to the left

R(2)C(2) means the cell relatively 2 rows down, 2 rows to the right

I prefer that notation because it outlines especially the relative addresses more clearly (for my weird little brain, at least).

Anyhow, Excel stores formulas internal in it's own binary fashion and reformats it for display to the user - you could change the notation in [File]->[Options]->[Formul

The term

For the coloring, I would try the "conditional formatting" feature ... try to google "Excel 2010 conditional formatting" for some useful links ...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

I don't know if I got your logic correct, but I've attached your excel file with additonal formulas in sheet3. Even while probably not the exact logic, they should lead the way how to do that.

stock-zero-coz-oos-or-new-exampl.xlsx