I have a column of numbers in a1 to a100. I would like to do an if test in
column B. If the value of the number in column A is less than 100 I want the
value resulting from the if test to be the value in column A. If however the
value in column A is greater than or equal to 100 I want the corresponding
cell in column B to be empty.

I know how to do an If function, but what I am not sure about is how to make
the False Value in the If function result in a cell being empty (I don't want
0 or "").

The reason I want to do this is so that when I chart this data from column B
on a line chart I want those values in column B that are empty not to have a
marker on the chart.

Thanks for any help.

The "" will leave the cell empty
=IF(A1<100,A1,"")

"JoeP" wrote:

I have a column of numbers in a1 to a100. I would like to do an if test in
column B. If the value of the number in column A is less than 100 I want the
value resulting from the if test to be the value in column A. If however the
value in column A is greater than or equal to 100 I want the corresponding
cell in column B to be empty.

I know how to do an If function, but what I am not sure about is how to make
the False Value in the If function result in a cell being empty (I don't want
0 or "").

The reason I want to do this is so that when I chart this data from column B
on a line chart I want those values in column B that are empty not to have a
marker on the chart.

Thanks for any help.

Let's solve the real problem: charting
In B1 enter =IF(A1<100,A1,NA())
Now some B cells will display #N/A which the chart engine will ignore
If you do not like the appearance of the data, use conditional formatting to
hide the #N/A
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"JoeP" wrote in message
...
I have a column of numbers in a1 to a100. I would like to do an if test in
column B. If the value of the number in column A is less than 100 I want
the
value resulting from the if test to be the value in column A. If however
the
value in column A is greater than or equal to 100 I want the corresponding
cell in column B to be empty.

I know how to do an If function, but what I am not sure about is how to
make
the False Value in the If function result in a cell being empty (I don't
want
0 or "").

The reason I want to do this is so that when I chart this data from column
B
on a line chart I want those values in column B that are empty not to have
a
marker on the chart.

Thanks for any help.

On Nov 19, 5:22*am, Mike wrote:
The "" will leave the cell empty
=IF(A1<100,A1,"")

I beg to differ. By definition, the cell is not "empty"; it has a
value or formula. Test with ISBLANK(). (Sadly, a misnomer.)

Instead, that formula will cause the cell to be __displayed__ as if it
is empty (if A1 is not less than 100). But the fact is: the cell
will result in a text value, namely the null string. This has
consequences different from a truly empty cell.

For example, if that formula is in A2, the formula =A2*something
results in a #VALUE error (if A1 is not less than 100). In contrast,
if A2 were truly empty (no value or formula), =A2*something results in
zero. IMHO, Excel is inconsistent about how cells with null strings
are handled. Some functions treat those cells as zero; others return
a #VALUE error. Use the N() function around references to cells in
which you mix numeric and null-string values.

Be that as it may, the OP explicitly ruled out this solution.