How to create a formula that results in a blank cell if required

[Martin C]

I have a spreadsheet that is to be populated during the course of the year.

I have a column which totals the number of days worked on a particular task
and have copied the formula down the sheet for the number of entries
required.

Obviously, as the sheet is currently unpopulated, I do not want a whole load
of zeroes listed in this column when the other cells in the sheet (which are
used in the calculation) are currently empty.

ie) what I have is (as a simple example)

A B C
-------------------------------
Hours Total Hrs Total Days
37 37 5
0
0
0
etc.

What I want is some way of making the calculated result in column C
(calculated from column B) to look blank if there is no data in column A.
The reason I want it to be blank is because when I use the data to plot an
automated graph I do not want the line to plummet down to zero (ie first
point at 5, all other points at zero), but not to show the plotted point if
it is zero. I know that you can get the chart to not plot the value if the
cell is blank, but setting "" in the formula gives a null string rather than
blank, so zero is effectively plotted on the chart.

I hope I have made this clear.

Basically, how can I do something like:

IF((A2<>""),B2/7.4,<some way of getting blank here>)

This is all greatly simplified, but it gives you the idea of what I am
after.

Thanks for any help you can give
Martin

 

[Don Guillett]

=if(a2="","",myformula)

 

[Sandy Mann]

Martin,

You are almost there.

Use "" in place of <some way of getting blank here>


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Chris P]

=IF(SUM(A1:A2)=0,"",SUM(A1:A2))

will do it.

 

[Martin C]

Sorry guys, I seem not to have made the question clear. I know how to make
the calculated cell *look* blank, but using "" as the else clause of the IF
statement adds a null string to the cell which is then not declared blank
any more. I know that because an ISBLANK test on this cell comes up as
False. As a result, the graph plots this point as a zero even if I have the
"Plot empty cells as - not plotted" option turned on. What I am after is a
true Blank present in the plot data so that only defined values are plotted
in the chart and everything else is ignored.

Martin

 

[RagDyeR]

XL will *not* graph the #N/A error ... so, use something like this in Column
C:

=If(A2+B2=0,NA(),A2+B2)
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Sorry guys, I seem not to have made the question clear. I know how to make
the calculated cell *look* blank, but using "" as the else clause of the IF
statement adds a null string to the cell which is then not declared blank
any more. I know that because an ISBLANK test on this cell comes up as
False. As a result, the graph plots this point as a zero even if I have the
"Plot empty cells as - not plotted" option turned on. What I am after is a
true Blank present in the plot data so that only defined values are plotted
in the chart and everything else is ignored.

Martin

 

[Martin C]

Agreed. I have already come across this approach, but did not consider it as
the sheet looks awful will a whole list of #NA details given in the column
that is being plotted.
Thanks for the suggestion though.
Martin

 

[KC Rippstein]

Use conditional formatting on that column to change the font to white when
the value is #NA