Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel - Formula to check if NULL or 0

Posted on 2014-04-14
Medium Priority
508 Views
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 probably have not explained it well but I hope someone may understand.

Thanks
John
stock-zero-coz-oos-or-new.xlsx
0
Question by:johnhill2312
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2

LVL 19

Expert Comment

ID: 39998626
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

Author Comment

ID: 39998669
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

LVL 14

Accepted Solution

frankhelk earned 1000 total points
ID: 39998767
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

LVL 19

Assisted Solution

regmigrant earned 1000 total points
ID: 39998857
+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

Author Comment

ID: 40001638

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

Author Comment

ID: 40004350
Hi Frank

>> 'prod-sales-history'!RC<>0
0

LVL 14

Expert Comment

ID: 40004456
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

Author Comment

ID: 40006197
thank you so much for your explanation!!
0

LVL 14

Expert Comment

ID: 40006472
You're welcome .... ;-)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (â€śforceâ€ť) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;â€¦
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month8 days, 15 hours left to enroll