How to put an Empty Cell in an IF function...

[Jeff Harbin]

Let me try this a different way...

I am using Excel 2003 and I've got a chart that is linked to data
located on another sheet within the same workbook. It is a weekly
production report and it always records a 0 for the weekends since we do
not produce anything on Saturday or Sunday. The data is extracted from
another source - not manually entered - and will always have the
weekends included. The chart is structured such that the 'Y' axis is
comprised of the values on the Data worksheet in column X, rows 14
through 49 ($X$14:$X$49) and represent the production yields. The 'X'
axis is taken from the Data worksheet in column A, rows 14 through 49
($A$14:$A$49) and represents the production date.

I know that if I clear the contents of the cells that correspond to the
weekends with a 0 production value and the cells that correspond to the
date identifier, I will get exactly what I want.

I conspired to add 2 more columns to the Data worksheet - Adjusted_Date
& Adjusted_Yield. I planned to populate these cells with the
corresponding values from Date & Yield. I thought if I could assign the
new columns - Adjusted_Date & Adjusted_Yield based on the Yield field, I
could get the formatting I want.

However, I am unsure of how to write a formula that will assign an Empty
cell based on the value of another cell.

For example: If the Yield value in Row 14 is 12%, I want the
Adjusted_Yield to be 12% and the Adjusted_Date to be equal to the value
of Date. On the other side...if the Yield value in Row 14 was 0%, I
want the Adjusted_Yield and the Adjusted_Date to be empty.

Or...am I making this too complicated and there's an easier way to
accomplish what I want.

Thanks.

Jeff

 

[joeu2004]

For example: If the Yield value in Row 14 is 12%, I want the
Adjusted_Yield to be 12% and the Adjusted_Date to be equal to the value
of Date. On the other side...if the Yield value in Row 14 was 0%, I
want the Adjusted_Yield and the Adjusted_Date to be empty.

Strictly speaking, you can assign a cell to "empty". "Empty" means
there is nothing in the cell: no value, no formula.

You __can__ have a null string ("") as the result. For example, in
the cell for Adjusted_Date:

=if(Yield_value = 12%, Date, "")

Caveat: Excel does not always interpret the null string ("") the same
way that it interprets an empty cell. I cannot think of an example
off-hand. But the point is: you cannot use the ISBLANK() function.
Instead, you test for the null string directly. For example, in
another cell:

=if(Adjusted_Date = "", "", something_else)

 

[Jeff Harbin]

Ok. That's what I was afraid of.

Do you know of something I can do with the Chart to skip values that are
equal to zero?

I'm grasping at straws but it's really annoying....

Thanks,

 

[Dave Peterson]

You could use a formula like:

=if(a14="",na(),12%)
=if(a14="",na(),theadjusteddatehere)

Charts will not show the =na() values as 0.

You could either use this in another (hidden???) column--or use conditional
formatting to hide the N/A's (white font on a white fill???) so that the cell
looks empty.