2010 Max-If with Multiple Value Ranges

[InfrequentVisitor]

Hello,

Very simply, there are sections of my data I want to exclude in my Max- and Min-If statements, but these sections are not continuous.

These formulas work for excluding all account numbers less than 9700000:

=MAX(IF(Table1[Year]=$A$2,IF(Table1[Month]=$B$2,IF(Table1[Section]=$E$2,IF(Table1[Account]<9700000,Table1[Solde/Balance])))))
=MIN(IF(Table1[Year]=$A$2,IF(Table1[Month]=$B$2,IF(Table1[Section]=$E$2,IF(Table1[Account]<9700000,Table1[Solde/Balance])))))


I also want to exclude account numbers equal to and between 6805000 and 6818000 (<6805000, >6818000).
I thought the following formula would work, but it returns $0 and I know it's not correct:

=MAX(IF(Table1[Year]=$A$2,IF(Table1[Month]=$B$2,IF(Table1[Section]=$E$2,IF(Table1[Account]<9700000,IF(Table1[Account]<6805000,IF(Table1[Account]>6818000,Table1[Solde/Balance])))))))


I was testing the viability of what I had done by reversing the signs, and found that I could return the correct value in-between the account numbers I want to exclude:

=MAX(IF(Table1[Year]=$A$2,IF(Table1[Month]=$B$2,IF(Table1[Section]=$E$2,IF(Table1[Account]<9700000,IF(Table1[Account]>=6805000,IF(Table1[Account]<=6818000,Table1[Solde/Balance])))))))

Does Excel only recognize one range at a time for a column? Any help would be appreciated.

Respectfully,

Eric

 

[jarjarbingie]

Hello,

Very simply, there are sections of my data I want to exclude in my Max- and Min-If statements, but these sections are not continuous.

These formulas work for excluding all account numbers less than 9700000:

=MAX(IF(Table1[Year]=$A$2,IF(Table1[Month]=$B$2,IF(Table1[Section]=$E$2,IF(Table1[Account]<9700000,Table1[Solde/Balance])))))
=MIN(IF(Table1[Year]=$A$2,IF(Table1[Month]=$B$2,IF(Table1[Section]=$E$2,IF(Table1[Account]<9700000,Table1[Solde/Balance])))))


I also want to exclude account numbers equal to and between 6805000 and 6818000 (<6805000, >6818000).
I thought the following formula would work, but it returns $0 and I know it's not correct:

=MAX(IF(Table1[Year]=$A$2,IF(Table1[Month]=$B$2,IF(Table1[Section]=$E$2,IF(Table1[Account]<9700000,IF(Table1[Account]<6805000,IF(Table1[Account]>6818000,Table1[Solde/Balance])))))))


I was testing the viability of what I had done by reversing the signs, and found that I could return the correct value in-between the account numbers I want to exclude:

=MAX(IF(Table1[Year]=$A$2,IF(Table1[Month]=$B$2,IF(Table1[Section]=$E$2,IF(Table1[Account]<9700000,IF(Table1[Account]>=6805000,IF(Table1[Account]<=6818000,Table1[Solde/Balance])))))))

Does Excel only recognize one range at a time for a column? Any help would be appreciated.

Respectfully,

Eric

=MAX(IF((Table1[Year]=$A$2)*(Table1[Month]=$B$2)*(Table1[Section]=$E$2)*(Table1[Account]<9700000)*(NOT((Table1[Account]>=6805000)*(Table1[Account]<=6818000))),Table1[Solde/Balance]))

 

[InfrequentVisitor]

This does it. Counter-intuitive: would've never guessed. Thanks Jarjar.