Excel - No Value in a cell through a formula 3

[jabrony76]

Ok here goes...

How do I write a formula in the [red]"destination cell"[/red] to look up the value of a "data-entry cell" on another sheet WITHOUT giving the [red]"destination cell"[/red] a value if the "data-entry cell" is blank.

I've attempted:
=IF('Data Entry 2001'!A1=0," ",'Data Entry 2001'!A1)
=IF('Data Entry 2001'!A1=0,,'Data Entry 2001'!A1)
=IF('Data Entry 2001'!A1=0,0,'Data Entry 2001'!A1)
='Data Entry 2001'!A1

and all of these formulas still end up giving the destination cell a zero value that will show up in a graph. I normally wouldn't mind this but in this instance, I have a trendline and the zero value is affecting the run of the trend line.

Basically, I need to know if it's possible to write a formula that will return a no value what so ever if the data entry cell is blank.

Thanks in advance for any help

Andy

 

[ETID]

have you tried it without the space?


=IF('Data Entry 2001'!A1=0,"",'Data Entry 2001'!A1)

 

[logius]

You might also need to set your chart options to treat empty cells as blanks instead of zeros. To do this, click on the current chart, then go to Chart>Chart Options.... From there you can set how it will treat blank data. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[jabrony76]

ETID - I tried that but to no avail...

LOGIUS - When I go into Chart>Chart Options... there is no option to on how to treat empty cells, only Titles, Axes, Gridlines, Legend, Data Labels, and Data Table.

I did go select the chart and go to Tools>Options>Chart and there are settings there that say to Plot Empty cells as either Not Plotted or Zero. I've tried both settings (originally it was on not plotted so it should have worked correctly) and no change, the chart is still seeing the cell with the formula in it as a cell with some value.

 

[logius]

My suggestion was only if you succeeded in getting blanks but the chart decided to treat them as zeros. As for your first question, sorry, but I don't know enough about Excel functions to help any. :-I ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[Hasit]

There is another option to this. Because you are adding a trendline, you need to ensure (or at least guess) that the empty cells *could* have a value that falls roughly between data points that DO have a value.

If you assume that this is the case, then try this formula. You interpolate the data for missing information. It has worked for me in the past (setting up the assumptions as I have stated):

In cells:

A1:1
A2:2
A3:
A4:
A5:5

Then insert this formula in say cell B1:

=($A$5-$A$2)/(ROW($A$5)-ROW($A$2))

Then in cell A3 above, insert the following:

=A2+$B$1

Use the same handle for cell A4.

You should then see:

A1: 1
A2: 2
A3: 3
A4: 4
A5: 5

So in your example above, your formula in cell A3 would be:

=IF(Sheet2!A3=0,A2+$B$1,Sheet2!A3)

 

[Hasit]

Actually, I have a better solution to your woes. As I was typing my last post, a colleague of mine wandered up and suggested this:

=IF(Sheet2!A3=0,NA(),Sheet2!A3)

If you use the NA() function, although empty cells will return #NA in the cell, the graph will ignore this and plot a proper line, which if you add a trend line to it will look perfect!

 

[Dreamboat]

To get rid of the #NA as suggested by Hasit, use conditional formatting on the cell...there you may be able to say if the cell is =#N/A, to make it white text.

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[tsuji]

Hello, jabrony76.

You can use a construction like:

=IF('Data Entry 2001'!A1=0,NA(),'Data Entry 2001'!A1)

The result of it will be understood by the charting and regression/trendline. The effect data is non-existence for the chart at that point.

regards - tsuji

 

[jabrony76]

Hassit -

Using the NA function worked absolutely perfect with regards to a the chart and plot points being ignored, Thanks!!

...but on the other hand, it muffed up all my Average and Standard Deviation calculations on the sheet. All the averages and Standard Deviations now say #N/A same as the blank entries.

Any ideas on how to do fix this?

My Average Formula is a basic "=Average(A1:A12)" and the same for Standard Deviation.

Thanks again!!
Andy

 

[Hasit]

Hmmm. For Averages, you are gonna have to do it in three steps.

Step 1: Create a formula which counts instances of actual numbers (rather than #N/A). The formula you can use is:

=COUNTIF(A1:A5,">0&quot

Step 2: Add up all the rows that DO NOT contain #N/A. To do this, you're gonna have to create an array formula, and test each cell for #N/A. If it exists, don't include it in the addition. The formula is:

{=SUM(IF(ISNA($A$1:$A$5),0,$A$1:$A$5))}

(The curly brackets are obtained using CTRL-SHIFT-ENTER)

Step 3: Divide the result of step 2 by the result of step 1


For standard deviation, the steps are more complex, because you have to break down the stdev formula into steps and check each cell for #N/A. Once you have created the formulas though, its a simple step to copy them around.

So, in this case, you already have the number of whole numbers (unless you want to count instances of no number (i.e. #N/A)). If the second option is preferred, then the formula is simple:

{=STDEV(IF(ISNA($A$2:$A$6),0,$A$2:$A$6))}

But if you do not want to count instances of #N/A, then you need to perform 5 calculations to get the SQRT. See HELP for the formula breakdown.

 

[tsuji]

Hello again, jabrony76 and Hasit.

This is how we should get the statistics.

Suppose the data table consists of heading at A1 and B1. A-column downwards A2 to A6 are some names. B-column downwards B2 to B6 are numeric data that you want to get their simple statistics like avg or std. And B2 to B6 consists of the forumua as studied above which may contain missing data resulting in #N/A. The below is to deal with it in a non-dbase basis.

Say, at cell B8 calculate the sum, B9 the average, and at cell B10 the standard deviation. Then their formulas are:

B8 : an array formula
{=SUM(IF(ISNUMBER($B$2:$B$6),$B$2:$B$6))}
B9 : an array formula
{=AVERAGE(IF(ISNUMBER($B$2:$B$6),$B$2:$B$6))}
B10 : an array formula
{=STDEV(IF(ISNUMBER($B$2:$B$6),$B$2:$B$6))}

Other statistics can be similarly constructed on a one-line basis.

regards - tsuji

 

[Hasit]

The Average function and sum function above were ones that I was going to offer, but I didn't because in STDEV, you have two options, to include instances of no value, or to exclude instances of no value.

Thats why I suggested the 3 step method.

In either case, they would all work, depending on assumptions made.