Why do I get a #VALUE! error with this formula???

[chunt]

Hi,

I'm getting frustrated with this and hopefully someone can help me
out.

Here is my data:

A B
C D
Hang up after accepting call AH 1 (BLANK CELL)


This is the formula I'm trying to get to work, but am getting #VALUE!
error

=OR(IF(ISBLANK(C6),TRUE,FALSE),IF(AND(C6=1, ISBLANK(D6)),"AH",FALSE))

The desired result would be to have AH display.

However if I were just to use the formula IF(AND(C6=1,
ISBLANK(D6)),"AH",FALSE) it displays correctly. So I know that it has
something to do with the or statement. If I replace "AH" with TRUE
then it works fine.

How can I get it to display "text" and not a boolean response?

Thanks,
Chris.

 

[chunt]

Oops, the columns got messed up on the display. The data is in
columns A,B,C and D

Thanks,
Chris.

 

[Bob Phillips]

=IF(OR(ISBLANK(C6),AND(C6=1, ISBLANK(D6))),"AH",FALSE)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sandy Mann]

I get TRUE returned by your formula.

If you want a blank looking cell when C6 is empty the simply:

=IF(AND(C6=1, ISBLANK(D6)),"AH","")

should do what you want.

--
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

 

[Harlan Grove]

(e-mail address removed) wrote...
....

Here is my data:

____________A____________B____C_____D
Hang up after accepting call__AH___1_____(BLANK CELL)

Tangent: underscores are a better compromise for proportional and
monospace typefaces.

This is the formula I'm trying to get to work, but am getting #VALUE!
error

=OR(IF(ISBLANK(C6),TRUE,FALSE),IF(AND(C6=1, ISBLANK(D6)),"AH",FALSE))

The desired result would be to have AH display.

....

Your formula returns #VALUE! because just before the outermost
function call, which is OR, it's equivalent to =OR(FALSE,"AH"). The OR
function can't convert the string "AH" into either TRUE or FALSE, and
that failure causes it to return the error.

This is where it helps to describe what you want in plain text. I'll
guess you actually want to display AH when C6=1 and D6 is blank,
otherwise display TRUE if C6 is blank, else display FALSE. If so, try

=IF(AND(C6=1,ISBLANK(D6)),"AH",ISBLANK(C6))

 

[chunt]

Thanks Harlan,

This was exactly what I needed. If however in the future I need to
add another text code into the mix and could have 3 distinct results I
guess I could no longer use this as the if statement can only produce
a true or false result.

For example

to display AH when C6=1 and D6 is blank,
to display TRUE if C6 is blank,
or display CP if C6=1 and D6=1

I ask because in the future I may have to add additional codes. Is
there a way to do this easily or does it require a completely
different formula for each and every code added?

Thanks for all of your help I really appreciate it

Cheers,
Chris.