Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

Excel - Formula to check if NULL or 0

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
0
johnhill2312
Asked:
johnhill2312
  • 4
  • 3
  • 2
2 Solutions
 
regmigrantCommented:
we can put in a formula to return null or 0 easily enough but what is it checking?
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?
0
 
johnhill2312Author Commented:
Hi regmigrant
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
0
 
frankhelkCommented:
Depending on what you wrote I would say you need an additional dara area for the results ... spearate coulumns und sheet2 or something in sheet3.

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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
regmigrantCommented:
+johnhill

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
0
 
johnhill2312Author Commented:
Ok thank you both for your valuable comments, really appreciate your suggestion frank...

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'!RC<>0,'prod-sales-history'!RC,IF(inventory!RC=0,AVERAGE(inventory!RC2:RC13),0))

ie: SetRedColor(IF(inventory!RC=0,AVERAGE(inventory!RC2:RC13),0)))

Many thanks
0
 
johnhill2312Author Commented:
Hi Frank
can you please advice what is RC in the formula

>> 'prod-sales-history'!RC<>0
0
 
frankhelkCommented:
Oh - sorry for that, I saved it in the (not so widley known) R1C1 format that I prefer for working with formulas. You can see the use of that notation by the fact that the columns are numbered like the rows instead of assigning them letters.

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]->[Formulas]->[R1C1 ....]. (I don't know the exact english titles because I use the german version of Excel, but that should point you to the right option)

The term  'prod-sales-history'!RC<>0 refers to "sheet  'prod-sales-history', cell with the same address as the cell this formula is in". Change the notation format and you'll see the meaning in your preferred fashion.

For the coloring, I would try the "conditional formatting" feature ... try to google "Excel 2010 conditional formatting" for some useful links ...
0
 
johnhill2312Author Commented:
thank you so much for your explanation!!
0
 
frankhelkCommented:
You're welcome .... ;-)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now