CODEWITHSUNDEEP
excelif-statement
Joey
Joey

Reputation: 934

Multiple IF functions in Excel: how to override on IF statement if another IF is true.

I have two columns of data. 

1 FALSE
2 FALSE
3 FALSE
4 FALSE
5 TRUE
6 TRUE
7 TRUE
8 TRUE

44.0099
16.043
30.0696
44.0972
58.1234
58.1234
72.1503
65.5401

I want to write an IF statement with multiple nested IFs such that: 

IF(8=TRUE,72.1503, IF(8 & 7 = True, 65.4501,.... IF( 8:1 = True, 44.0099)

However, when more cells are 'True', I want this to override the preceding IF statement.

For example, say if 8 and 7 are True, I want the result of this to override the result if just 8 is True and so on.... if 8 and 7 and 6 are True, I want this to override the result of that if just 8 and 7 are True.

Hope this makes sense?

Upvotes: 0

Views: 6623

Answers (2)

timbmg
timbmg

Reputation: 3328

You need to use the AND(logical1, [logical2], ...) function of Excel in your IF statements to concatenate more conditions. (read more about AND here: https://support.office.com/en-us/article/AND-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)

Also you need to turn the logic around. You need to check if all fields are TRUE first. Then if field 2-8 are TRUE and so on...

So your statement would look something like this:

IF(AND(A8=TRUE,A7=TURE, A6=TRUE, ...), 1,
    IF(AND(A8=TRUE,A7=TURE, ...), 2,
        IF(AND(A8=TRUE, ...), 3,)
    )
)

Upvotes: 1

Carsten Massmann
Carsten Massmann

Reputation: 28206

Try this:

=IF(AND(A3:A5)=TRUE,3,IF(AND(A3:A4)=TRUE,2,IF(A3=TRUE,1)))

or, even simpler:

=IF(AND(A3:A5),3,IF(AND(A3:A4),2,IF(A3,1)))

assuming that your data is in cells A3:A5 like:

Output:

A3:A5:     result
TRUE 
TRUE
TRUE  -->  3

A3:A5:     result
TRUE 
TRUE
FALSE -->  2

A3:A5:     result
TRUE 
FALSE
FALSE -->  1

The if statement starts with the "higher priority" conditions and slowly works its way down to the mode basic ones.

Upvotes: 1

Related Questions