Handling Dashes in IF statement
 

Hello,

I have a vlookup statement that, when the table is empty, it returns a -
(dash).

Dependant upon that vlookup cell, I have the following IF statement
=IF(B51="",NA(),B57)

The IF statement does not work because of the dashes, but I have tried other
alternatives in the "" to recognise the dashes, but none work.

How do I modify the IF statement to recognise the dash as "no entry" and
return the N/A. Once the N/A is returned, a conditional format changes the
font colour to the same as the background. I am using the N/A to avoid my
graph showing the line at zero.

Thanks in advance.
Ant

Handling Dashes in IF statement
 

From what you've told us, it's as simple as checking for a dash, as in:
=IF(B51="-",NA(),B57)

The next question is: why have the Vlookup return a dash? Why not have it
return #N/A? That would save you a step.

Finally, is it really returning a dash, or is it something formatted as a
dash? Formatting won't change the underlying value. In your If statement,
you need to ignore formatting, and check for the value actually being
returned.

Regards,
Fred

"Ant" wrote in message
...
Hello,

I have a vlookup statement that, when the table is empty, it returns a -
(dash).

Dependant upon that vlookup cell, I have the following IF statement
=IF(B51="",NA(),B57)

The IF statement does not work because of the dashes, but I have tried
other
alternatives in the "" to recognise the dashes, but none work.

How do I modify the IF statement to recognise the dash as "no entry" and
return the N/A. Once the N/A is returned, a conditional format changes the
font colour to the same as the background. I am using the N/A to avoid my
graph showing the line at zero.

Thanks in advance.
Ant

Handling Dashes in IF statement
 

Hi Fred,

Your first suggestion I have already attempted and it did not work. VLookup
is returning a dash for a 0 value in the lookup table, but i don't know what
the underlying value is to place into the If statement.

I am not asking Vlookup to return a dash. I have changed the format to
return a zero or the N/A, but it does not solve the problem

Yes, you hit the nail on the head. That is what I am asking.... What is the
actual value being returned so that way I can place it in my If statement. Or
is there another method to do this?

Ant


"Fred Smith" wrote:

From what you've told us, it's as simple as checking for a dash, as in:
=IF(B51="-",NA(),B57)

The next question is: why have the Vlookup return a dash? Why not have it
return #N/A? That would save you a step.

Finally, is it really returning a dash, or is it something formatted as a
dash? Formatting won't change the underlying value. In your If statement,
you need to ignore formatting, and check for the value actually being
returned.

Regards,
Fred

"Ant" wrote in message
...
Hello,

I have a vlookup statement that, when the table is empty, it returns a -
(dash).

Dependant upon that vlookup cell, I have the following IF statement
=IF(B51="",NA(),B57)

The IF statement does not work because of the dashes, but I have tried
other
alternatives in the "" to recognise the dashes, but none work.

How do I modify the IF statement to recognise the dash as "no entry" and
return the N/A. Once the N/A is returned, a conditional format changes the
font colour to the same as the background. I am using the N/A to avoid my
graph showing the line at zero.

Thanks in advance.
Ant

.

Handling Dashes in IF statement
 

It sounds as if the nitty-gritty is where Fred said:
" Finally, is it really returning a dash, or is it something formatted as a
dash? Formatting won't change the underlying value. In your If statement,
you need to ignore formatting, and check for the value actually being
returned."
It sounds as if you are returning a zero and in the relevant cell you are
formatting it as a dash.
If so, your IF statement should be =IF(B51=0,NA(),B57)
--
David Biddulph


"Ant" wrote in message
...
Hi Fred,

Your first suggestion I have already attempted and it did not work.
VLookup
is returning a dash for a 0 value in the lookup table, but i don't know
what
the underlying value is to place into the If statement.

I am not asking Vlookup to return a dash. I have changed the format to
return a zero or the N/A, but it does not solve the problem

Yes, you hit the nail on the head. That is what I am asking.... What is
the
actual value being returned so that way I can place it in my If statement.
Or
is there another method to do this?

Ant


"Fred Smith" wrote:

From what you've told us, it's as simple as checking for a dash, as in:
=IF(B51="-",NA(),B57)

The next question is: why have the Vlookup return a dash? Why not have it
return #N/A? That would save you a step.

Finally, is it really returning a dash, or is it something formatted as a
dash? Formatting won't change the underlying value. In your If statement,
you need to ignore formatting, and check for the value actually being
returned.

Regards,
Fred

"Ant" wrote in message
...
Hello,

I have a vlookup statement that, when the table is empty, it returns
a -
(dash).

Dependant upon that vlookup cell, I have the following IF statement
=IF(B51="",NA(),B57)

The IF statement does not work because of the dashes, but I have tried
other
alternatives in the "" to recognise the dashes, but none work.

How do I modify the IF statement to recognise the dash as "no entry"
and
return the N/A. Once the N/A is returned, a conditional format changes
the
font colour to the same as the background. I am using the N/A to avoid
my
graph showing the line at zero.

Thanks in advance.
Ant

.

Handling Dashes in IF statement
 

Thanks David and Fred.

It was a Zero. I think when I tried the zero, I must have left the inverted
commas around it.

Regards
Ant


"David Biddulph" wrote:

It sounds as if the nitty-gritty is where Fred said:
" Finally, is it really returning a dash, or is it something formatted as a
dash? Formatting won't change the underlying value. In your If statement,
you need to ignore formatting, and check for the value actually being
returned."
It sounds as if you are returning a zero and in the relevant cell you are
formatting it as a dash.
If so, your IF statement should be =IF(B51=0,NA(),B57)
--
David Biddulph


"Ant" wrote in message
...
Hi Fred,

Your first suggestion I have already attempted and it did not work.
VLookup
is returning a dash for a 0 value in the lookup table, but i don't know
what
the underlying value is to place into the If statement.

I am not asking Vlookup to return a dash. I have changed the format to
return a zero or the N/A, but it does not solve the problem

Yes, you hit the nail on the head. That is what I am asking.... What is
the
actual value being returned so that way I can place it in my If statement.
Or
is there another method to do this?

Ant


"Fred Smith" wrote:

From what you've told us, it's as simple as checking for a dash, as in:
=IF(B51="-",NA(),B57)

The next question is: why have the Vlookup return a dash? Why not have it
return #N/A? That would save you a step.

Finally, is it really returning a dash, or is it something formatted as a
dash? Formatting won't change the underlying value. In your If statement,
you need to ignore formatting, and check for the value actually being
returned.

Regards,
Fred

"Ant" wrote in message
...
Hello,

I have a vlookup statement that, when the table is empty, it returns
a -
(dash).

Dependant upon that vlookup cell, I have the following IF statement
=IF(B51="",NA(),B57)

The IF statement does not work because of the dashes, but I have tried
other
alternatives in the "" to recognise the dashes, but none work.

How do I modify the IF statement to recognise the dash as "no entry"
and
return the N/A. Once the N/A is returned, a conditional format changes
the
font colour to the same as the background. I am using the N/A to avoid
my
graph showing the line at zero.

Thanks in advance.
Ant

.


.