Nested IF and ISTEXT Statments

**Kwarren** · 01-15-2011, 09:53 AM

Is there a way to shorten the following statements?

=IF(AND(H5=0,K5>0),0,IF(AND(K5=0,H5>0),0,IF(AND(AND(H5=0,J5>0,K5=0)),0

=IF(ISTEXT(E5),"TEXT ERROR",IF(ISTEXT(H5),"TEXT ERROR",IF(ISTEXT(K5),"TEXT ERROR"

I need to add both to a much longer formula but then I exceed the maximum number of nested statments.

Thanks,

Kirk

**Richard Buttrey** · 01-15-2011, 10:20 AM

Hi,
Both your formulae are missing closing parentheses, and don't seem to have a default parameter. What I think you;re looking for are:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

where "XX" is the default which you may or may not want to be '0'.

Rgds

**Kwarren** · 01-15-2011, 11:08 AM

Thanks Richard,

I'm getting close to what I want with the following formula:

=IF(AND(H5=0,K5>0),0,IF(AND(K5=0,H5>0),0,IF(AND(AND(H5=0,J5>0,K5=0)),0,IF(OR(OR(COUNTIF(E5,"*"),COUNTIF(H5,"*"),COUNTIF(K5,"*"))),"TEXT ERROR"

However, when I enter text into cells I get a zero value instead of the "TEXT ERROR."
how can I make this happen?

Thanks again!!

Kirk

**Richard Buttrey** · 01-15-2011, 11:29 AM

Hi,

It's not clear exactly what you're trying to do. The formula you post is still missing some closing parentheses, and you're still using IFs where I believe you can simplify with progressive AND(x=y,x=z) type statements as I mentioned in my previous post. All of which makes it difficult to read, particularly without seeing the formula in the context of its workbook.

Upload the workbook and manually add several results that you expect to see as a result of data on the sheet, and add some notes as to how you've calculated them. We'll then stand a better chance of simplifying the thing.

Rgds

**martindwilson** · 01-15-2011, 11:39 AM

another way for text error would be
=IF(ISERROR(E5+H5+K5),"text error","xx")

**TechRetard** · 01-15-2011, 01:10 PM

Originally Posted by martindwilson

another way for text error would be
=IF(ISERROR(E5+H5+K5),"text error","xx")

you may want to use if error.type = 3 to be more specific

**Kwarren** · 01-15-2011, 03:21 PM

Richard and Martin,

Both of these suggestions worked as I needed them to (actually, better). And I still have one IF statement available if needed.

Richard, I believe I must have made a mistake in my copy and paste from your reply. Sorry about that.

(This formula is part of a very elaborate monthly timesheet I’ve been creating. With the addition of these statements, this project is complete!)

Thanks you both so much for your help!
Kirk