IF AND ISTEXT ISBLANK formula   

I have two columns EA and EB, if EA istext and EB istext then EB, AND if EA 
isblank and EB istext then EB, AND if EA istext and EB isblank then EA. I 
wrote the formula below but it isn't returning values as I thought I had 
written it. Your help is greatly appreciated.

=IF(AND(ISTEXT(EA6),ISTEXT(EB6)),EB6,IF(AND(ISBLANK(EA6),ISTEXT(EB6)),EB6,IF(AND(ISTEXT(EA6),ISBLANK(EB6)),EA6,)))
-- 
Thank you,

scrowley(AT)littleonline.com

date: Mon, 6 Oct 2008 12:23:01 -0700   author:   SCrowley

RE: IF AND ISTEXT ISBLANK formula   

the formula is entered as you were expecting. Not sure which error you're 
getting. Try clicking on the fx vutton to see where you may be getting your 
error...

"SCrowley" wrote:

> I have two columns EA and EB, if EA istext and EB istext then EB, AND if EA 
> isblank and EB istext then EB, AND if EA istext and EB isblank then EA. I 
> wrote the formula below but it isn't returning values as I thought I had 
> written it. Your help is greatly appreciated.
> 
> =IF(AND(ISTEXT(EA6),ISTEXT(EB6)),EB6,IF(AND(ISBLANK(EA6),ISTEXT(EB6)),EB6,IF(AND(ISTEXT(EA6),ISBLANK(EB6)),EA6,)))
> -- 
> Thank you,
> 
> scrowley(AT)littleonline.com

date: Mon, 6 Oct 2008 12:30:41 -0700   author:   Sean Timmons

RE: IF AND ISTEXT ISBLANK formula   

Thank you, Sean. After poking around I found a hidden " ' " mark in the blank 
cells. I did a Find and Replace as well as TRIM and CLEAN and neither one 
removed the mark so I'm clearing contents of visually blank cells. Wow, I 
actually wrote a working formula!!!
-- 
Thank you,

scrowley(AT)littleonline.com


"Sean Timmons" wrote:

> the formula is entered as you were expecting. Not sure which error you're 
> getting. Try clicking on the fx vutton to see where you may be getting your 
> error...
> 
> "SCrowley" wrote:
> 
> > I have two columns EA and EB, if EA istext and EB istext then EB, AND if EA 
> > isblank and EB istext then EB, AND if EA istext and EB isblank then EA. I 
> > wrote the formula below but it isn't returning values as I thought I had 
> > written it. Your help is greatly appreciated.
> > 
> > =IF(AND(ISTEXT(EA6),ISTEXT(EB6)),EB6,IF(AND(ISBLANK(EA6),ISTEXT(EB6)),EB6,IF(AND(ISTEXT(EA6),ISBLANK(EB6)),EA6,)))
> > -- 
> > Thank you,
> > 
> > scrowley(AT)littleonline.com

date: Mon, 6 Oct 2008 13:36:01 -0700   author:   SCrowley

Re: IF AND ISTEXT ISBLANK formula   

If those cells contain formulas that might return formula blanks ("") then 
ISBLANK might not be doing what you think it's doing.

ISBLANK means "is empty". If a cell contains a formula blank then the cell 
*isn't empty* and ISBLANK evaluates to FALSE.

Also, a formula blank is a *text string* so ISTEXT will evaluate to TRUE.

-- 
Biff
Microsoft Excel MVP


"SCrowley"  wrote in message 
news:48B98A2B-7DAE-470B-8799-0C3682275170@microsoft.com...
>I have two columns EA and EB, if EA istext and EB istext then EB, AND if EA
> isblank and EB istext then EB, AND if EA istext and EB isblank then EA. I
> wrote the formula below but it isn't returning values as I thought I had
> written it. Your help is greatly appreciated.
>
> =IF(AND(ISTEXT(EA6),ISTEXT(EB6)),EB6,IF(AND(ISBLANK(EA6),ISTEXT(EB6)),EB6,IF(AND(ISTEXT(EA6),ISBLANK(EB6)),EA6,)))
> -- 
> Thank you,
>
> scrowley(AT)littleonline.com

date: Mon, 6 Oct 2008 16:51:37 -0400   author:   T. Valko

RE: IF AND ISTEXT ISBLANK formula   

I guess that's a good thing. :-)

Not sure why a find/replace wouldn't have fixed the issue, unless it did too 
much replacing....



"SCrowley" wrote:

> Thank you, Sean. After poking around I found a hidden " ' " mark in the blank 
> cells. I did a Find and Replace as well as TRIM and CLEAN and neither one 
> removed the mark so I'm clearing contents of visually blank cells. Wow, I 
> actually wrote a working formula!!!
> -- 
> Thank you,
> 
> scrowley(AT)littleonline.com
> 
> 
> "Sean Timmons" wrote:
> 
> > the formula is entered as you were expecting. Not sure which error you're 
> > getting. Try clicking on the fx vutton to see where you may be getting your 
> > error...
> > 
> > "SCrowley" wrote:
> > 
> > > I have two columns EA and EB, if EA istext and EB istext then EB, AND if EA 
> > > isblank and EB istext then EB, AND if EA istext and EB isblank then EA. I 
> > > wrote the formula below but it isn't returning values as I thought I had 
> > > written it. Your help is greatly appreciated.
> > > 
> > > =IF(AND(ISTEXT(EA6),ISTEXT(EB6)),EB6,IF(AND(ISBLANK(EA6),ISTEXT(EB6)),EB6,IF(AND(ISTEXT(EA6),ISBLANK(EB6)),EA6,)))
> > > -- 
> > > Thank you,
> > > 
> > > scrowley(AT)littleonline.com

date: Mon, 6 Oct 2008 14:40:08 -0700   author:   Sean Timmons

Re: IF AND ISTEXT ISBLANK formula   

Which would mean instead of ISBLANK, do EA=""

"T. Valko" wrote:

> If those cells contain formulas that might return formula blanks ("") then 
> ISBLANK might not be doing what you think it's doing.
> 
> ISBLANK means "is empty". If a cell contains a formula blank then the cell 
> *isn't empty* and ISBLANK evaluates to FALSE.
> 
> Also, a formula blank is a *text string* so ISTEXT will evaluate to TRUE.
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "SCrowley"  wrote in message 
> news:48B98A2B-7DAE-470B-8799-0C3682275170@microsoft.com...
> >I have two columns EA and EB, if EA istext and EB istext then EB, AND if EA
> > isblank and EB istext then EB, AND if EA istext and EB isblank then EA. I
> > wrote the formula below but it isn't returning values as I thought I had
> > written it. Your help is greatly appreciated.
> >
> > =IF(AND(ISTEXT(EA6),ISTEXT(EB6)),EB6,IF(AND(ISBLANK(EA6),ISTEXT(EB6)),EB6,IF(AND(ISTEXT(EA6),ISBLANK(EB6)),EA6,)))
> > -- 
> > Thank you,
> >
> > scrowley(AT)littleonline.com 
> 
> 
>

date: Mon, 6 Oct 2008 15:42:10 -0700   author:   Sean Timmons