Help on conditional IF statement nested with ISTEXT.

**DorothyFan1** · 12-30-2010, 11:17 AM

I need to write a conditional formula that uses the OR function and nested inside an IF function is the ISTEXT logical condition checking to see if a cell has text. If it finds the cell has text, the condition tells the formula to check to see if it says "ATM" OR "Cash". If it satisfies either condition...the formula subtracts a certain amount from the original balance.

When I've tried to write such a formula, I always get a !VALUE! error or formula error creation errors. Thanks in advance.

**DonkeyOte** · 12-30-2010, 11:38 AM

It would help if we had some range refereneces with which to work but let's assume numeric in A1 (balance of some sort) and text (possibly) in B1 with result to go in C1

Please Login or Register  to view this content.

this will Subtract the numeric value of B1 from A1 unless B1 is ATM or Cash at which point an arbitrary value of 10 is deducted.

Adjust as necessary

FWIW, #VALUE! errors generally result from invalid coercion - eg 10-"apple" ... apple can not be coerced to a number (a formula Null [""] is the equiv. of "apple" - ie a non-numeric string)

**DorothyFan1** · 12-30-2010, 07:02 PM

This works okay...but I had to modify the formula to work in my spreadsheet. It turns out what I really need is a nested IF function because I have two separate conditions that could come up in the worksheet.

For example..here's my modified formula based on your suggestion:

=$G$11-IF(ISTEXT(E13={"paid"}),FALSE(),SUM(D13))

But it turns out if the cell that I've assigned to have text values lists an answer called "Credit"..I'm trying to figure out how to accomplish this without adding another column to the worksheet. Otherwise I'd love to be able to add another conditional IF statement to the above formula using the condition for "Credit" to be nested inside it and apply a different result.

Again, thanks in advance.

**DonkeyOte** · 12-30-2010, 07:08 PM

I'm not really sure how the above relates to the initial question and to be honest it doesn't make a great deal of sense to me:

Please Login or Register  to view this content.

will always yield a FALSE result given the Boolean generated within parentheses is not Text

Given the above it follows you are always (in effect) subtracting SUM(D13) and therefore the below would generate the exact same result:

Please Login or Register  to view this content.

Perhaps best to post a sample file which illustrates set up via sample data whilst also outlining desired results as I confess I'm struggling to follow.

**DorothyFan1** · 12-31-2010, 05:03 AM

You were right. It was doing subtraction twice regardless of result. I've re-designed the spreadsheet to account for this plus I've simplified the formula to make it easier for me to locate what I need. Thanks for the help.