Multiple IF Statements with Multiple Results

[NorthernFoz]

Hi,

I'm new here and would like a bit of help please.

I have a worksheet and in one of my columns I can have multiple values, this could either be a valid numeric value, .e.g "Trace" or a value such as >1.5 or it could be blank.

Depending on the value I then want to return a different result in another cell.

In the example I have tried my data is in cell E8

I have tried the following formula and failed miserably, I'm not sure whether IF or IF OR can cope with this.

=IF(E8>=0,"1",IF(E8<0,"Invalid",IF(E8="Trace","2",IF(ISBLANK(E8),"4",IF(LEFT(E8,1=">"),"5")))))

If E8 contains a numeric value equal or greater than 0 then I want it to return a value of 1, this works

If E8 contains a value that is negative then I want my formula to then display "Invalid"

If E8 contains the word "Trace" to return a value of 2

If E8 is blank then to return a value of 4

If the first character in E8 is ">" then return a value of 5

Can this be handled in a single formula or do I need to brush up on Macros?

Any help would be appreciated as this is doing my head in.

Thanks in advance

[vamosj]

The problem you encountered was that any input, either alpha or numeric is considered a value. So unless it was a negative number, all text was treated as >= 0. Rewrote the formula to put >= 0 at the end so it has to step through each other IF statement before making it to that one.


=IF(LEFT(E8,1)=">",5,IF(E8="",4,IF(E8="Trace",2,IF(E8<0,"Invalid",IF(E8>=0,1,"Error")))))

[ben_hensel]

In the order I noticed things:

1) You've got the syntax wrong on the LEFT expression, you need to move the right paren closing it.

2) When comparing with a math operator, excel error handing automatically assumes that blank cells assess as zero, and text assesses as greater than zero, so both of those will throw a "1" from the very first conditional.

This is easy to fix for you: stick a AND in the first conditional to check whether ISNUMBER(E8) as well.

3) If none of the conditions are met, it's returning FALSE because you left that condition implicit. I will make this code "6" in as a placeholder for whatever you want to return.

4) You are returning all these codes as text strings, not as numbers. Is that what you want to do? I don't think so because you're saying "value" of the number.

Result:

Please Login or Register  to view this content.

[NorthernFoz]

Thanks to both of you, that makes perfect sense now, I should've twigged really

[vamosj]

If this has solved the issue, please edit your Original Post and mark the thread as solved. And as always, please provide positive/negative feedback to those who answered the call.

Thank you and hope your day goes well.