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

  • Thread starter Thread starter Martin C
  • Start date Start date
M

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
 
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
 
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
 
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
 
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
 
Use conditional formatting on that column to change the font to white when
the value is #NA
 
Back
Top