CODEWITHSUNDEEP
excelexcel-formula
Sean
Sean

Reputation: 13

Combining formulas using AND

I am trying to combine two forumlas into one.

They both work individually and I would like to make it so the condition is only met if both formulas are met. The forumlas are:

=IF(AND(Sheet2!$C$6>40,Sheet2!$C$6<=50),TRUE,FALSE)


=IF((INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","3"))>=Sheet2!$B$6),TRUE,FALSE)

I have tried to use another AND clause but had no luck yet with this, would anyone be able to provide a consolidated formula for both of these?

Upvotes: 1

Views: 57

Answers (2)

Dirk Reichel
Dirk Reichel

Reputation: 7979

the long way:

=IF(AND(Sheet2!$C$6>40,Sheet2!$C$6<=50,INDIRECT("R3C"&COLUMN(),)>=Sheet2!$B$6), TRUE, FALSE)

the short way:

=AND(Sheet2!$C$6>40,Sheet2!$C$6<=50,INDIRECT("R3C"&COLUMN(),)>=Sheet2!$B$6)

also possible:

=((Sheet2!$C$6>40)*(Sheet2!$C$6<=50)*(INDIRECT("R3C"&COLUMN(),)>=Sheet2!$B$6))=1

why the SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","3")??? just ADDRESS(3,COLUMN(),4) will be the same... also why address at all? justINDIRECT("R3C"&COLUMN(),) will also work

Upvotes: 1

R3uK
R3uK

Reputation: 14537

Compiling the comments, this should be the minimal formula gathering your tests : 

=AND(Sheet2!$C$6>40,Sheet2!$C$6<=50,INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","3"))>=Sheet2!$B$6)

Upvotes: 0

Related Questions