I need some help creating an IF statement in Excel.

If there is text in (G19) then I would like it so that (T19) produces a result if (S19) meets the criteria of: if (S19) <=150 than 1.2, if (S19) <=230 than 1.575-(S19)/400, if (S19) <=430 than 1.0, if (S19) <=550 than 1.358-(S19/1200), if (S19) >550 than REJECT but if there is no text in (G19) than I would like the result in (T19) to produce a result if (S19) meets the criteria of: if (S19) <=430 than 1.0, if (S19) <=550 than 1.358-(S19)/1200, if (S19) >550 than REJECT.

Sorry, but it can't be done. If S19 <=150, then it is also <230 and <430 and <550.

You can't have T19 produce 4 different results at the same time.

Hello DerbyDad,

I have part of a formula that produces the result that I want it too in (T19). The only problem is that it still gives the same result whether or not (G19) has text in it. Is there any way to modify it to work? Here it is: =IF(S19="","",IF(ISTEXT(D19),"E",IF(NOT(ISNUMBER(D19)),IF(S19<=430,1,IF(S19<=550,ROUND(1.358-(S19/1200),3),"Reject")),IF(S19<=150,1.2,IF(S19<230,ROUND(1.575-(S19/400),3),IF(S19<=430,1,IF(S19<=550,ROUND(1.358-(S19/1200),3),"Reject")))))))

Also, if a cell is formatted to Number with 3 decimal places, then is it necessary to have that ROUND function in the formula?

re: "...gives the same result whether or not (G19) has text in it." Could that be because your formula never refers to G19?

re: The ROUND function

I don't think you need it. Why don't you just take it out and see?

Yes, I suppose that not referencing that cell would be a reason that it doesn't work.

Okay, when I change the ISTEXT function to reference (G19) is gives me an "E", which I see is the correct value if true. Is there a way to change the statement so that when there is text in (G19) that the true result is based on the part that begins IF(S19<=550... and the false result would be based on if (S19) <=430 than 1.0, if (S19) <=550 than 1.358-(S19)/1200, if (S19) >550 than REJECT?

First, what's this for? IF(NOT(ISNUMBER(D19))

Second, I'm really confused now...

You have 2 parts that contain "IF(S19<=550... " both of which are the value_if_false part of a Nested If. I'm not sure which one you are refering to when you say "the part that begins IF(S19<=550..."

Maybe if you gave some examples of actual values in D19, G19 (?) and S19 with the expected result it would be easier to understand.

Third, it's "if (S19) <=430

then1.0" not "if (S19) <=430than1.0".

First - I am not sure what that function is for. I didn't create the statement originally, it is just in a sheet that I am using, upon which I noticed that it doesn't work exactly as it should. Also, I don't think it needs the references to D# cells. Second - I was referring to the first one. Anyways, examples of results I would expect:

1. G19 is text, say an X, and S19 is any number from 1 to 150, then T19 is 1.200.

2. G19 is X, and S19 is any number from 151 to 229, then T19 is 1.575-S19/400.

3. G19 is X, and S19 is any number from 230 to 430, then T19 is 1.000.

4. G19 is X, and S19 is any number from 431 to 550, then T19 is 1.358-S19/1200.

5. G19 is X, and S19 is any number over 550, then T19 is Reject.

6. If there is no text in G19, and S19 is any number from 1 to 430, then T19 is 1.000.

7. If there is no text in G19, and S19 is any number from 431 to 550, then T19 is 1.358-S19/1200.

8. If there is no text in G19, and S19 is any number over 550, then T19 is Reject.Those are all the functions that I would like the statement to perform. The one that I already have works for 1 to 5 but needs to be edited (if it is possible) to work for 6 to 8 as well.

Thanks for the examples. Here's how I went about this...

First, based on your examples, I am assuming that only integers will appear in S19. Since your conditions jump from 229 to 230 and 430 to 431, I'm assuming 229.5 or 430.23 will never appear in S19.

Second, your lowest value for S19 is 1, so I'm also assuming that 0, -1, etc. are not valid entries.

Both of these issues can be addressed, but for the sake of simplicity, I'm going with those 2 assumptions.

OK, here we go...

It appears that you have some conditions whose desired results are not dependent on whether or not G19 contains text:

4 & 7 - "1.358-S19/1200" if 430 < S19 <551

5 & 8 - "Reject" if S19 > 550Therefore we can test for those conditions first, without worrying about G19:

IF(AND(S19>430,S19<551),1.358-S19/1200,IF(S19>550,"Reject",...That leaves us with with only one condition where G19 does not contain text - Item 6, whose result is "1" - so the only thing we have to test for is text, or actually, the absence thereof:

IF(NOT(ISTEXT(G19)),1,...If we've made it this far through the formula then S19 is not between 431 and 550, it's not over 550, and G19 must contain text. So now we'll test for Items 1 & 2. If neither of them are true, the only condition left is Item 3, whose result is "1":

IF(S19<151,1.2,IF(S19<230,1.575-S19/400,1))Combining all of those together we get:

=IF(AND(S19>430,S19<551),1.358-S19/1200,IF(S19>550,"Reject",IF(NOT(ISTEXT(G19)),1,IF(S19<151,1.2,IF(S19<230,1.575-S19/400,1)))))

First - The specification that I based the examples on is somewhat vague as there may be instances where the value in S19 may go to one or two decimal places. If there is a result like that, then I just need to put .99 or .01 after the applicable values in the statement, correct? Second - Again, the specification is vague but I don't believe that a value lower than 1 will ever be encountered.

Thanks for all the help.

To deal with values with decimal places, you could go back to <= and >= your terminal values. That would cover all instances, regardless of how many decimal places there are. Of course you would need to know if - for example - if 150.5 goes with the 150 criteria or the 151 criteria. As far as values <1, I think you need to know if that will occur, or at least deal with the possibility. The reason I say this is because my formula will give you a different result depending on whether there is text in G19 or not.

As I'm sure you know, with any project, unless we know the full specs, it's hard to come up with a full solution. We don't want to "overbuild" for situations that will never occur, but we do want to make the solution robust enough to handle every condition that

couldoccur.In your case, it might be worth the effort to find out if S19 could be <1 and what you want the result to be if it is.

Ask Your Question

Weekly Poll

Do you think broadband providers should eliminate all data caps?

Discuss in The Lounge

Poll History