Solved

Excel - Formula to check if NULL or 0

Posted on 2014-04-14
9
475 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
[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
  • Learn & ask questions
  • 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 14

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 

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 14

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 14

Expert Comment

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

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

627 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