Solved

Excel - Formula to check if NULL or 0

Posted on 2014-04-14
9
392 Views
Last Modified: 2014-04-17
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
Comment
Question by:johnhill2312
  • 4
  • 3
  • 2
9 Comments
 
LVL 19

Expert Comment

by:regmigrant
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

by:johnhill2312
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 13

Accepted Solution

by:
frankhelk earned 250 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

by:regmigrant
regmigrant earned 250 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:johnhill2312
ID: 40001638
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
 

Author Comment

by:johnhill2312
ID: 40004350
Hi Frank
can you please advice what is RC in the formula

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

Expert Comment

by:frankhelk
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

by:johnhill2312
ID: 40006197
thank you so much for your explanation!!
0
 
LVL 13

Expert Comment

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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now