Excel Formula using multiple conditions

Solution 1:

I'm sure that this will help you:

How it works:

• Create a Table like shown above and enter this formula in Column Category.

  =IF(AND([@Gender]="M",[@Age]<30),"M30",IF(AND([@Gender]="M",[@Age]>=23,[@Age]<=49),"M49",IF(AND([@Gender]="M",[@Age]>=50),"M50",IF(AND([@Gender]="F",[@Age]<30),"F30",IF(AND([@Gender]="F",[@Age]>=23,[@Age]<=49),"F49",IF(AND([@Gender]="F",[@Age]>=50),"F50",""))))))
  

How it works:

• Enter this array (CSE) formula in cell S36, finish with Ctrl+Shift+Enter, and fill across, then add new columns Points & Status.

  {=IFERROR(INDEX($S$27:$W$33,SMALL(IF((INDEX($S$27:$W$33,,2)="M"),MATCH(ROW($S$27:$W$33),ROW($S$27:$W$33)),""),ROWS(S36:$S$36)),COLUMNS($A$1:A1)),"")}
  

• Formula in cell X36:

  =(IF(AND($W36="M30",$V36<=22),0,IF(AND($W36="M30",$V36>=23,$V36<=49),1,IF(AND($W36="M30",$V36>=50),2,(IF(AND($W36="M49",$V36<=17),0,IF(AND($W36="M49",$V36>=18,$V36<=40),1,IF(AND($W36="M49",$V36>=27),2,(IF(AND($W36="M50",$V36<=9),0,IF(AND($W36="M50",$V36>=9,$V36<=26),1,IF(AND($W36="M50",$V36>=27),2,""))))))))))))
  

• Final formula in cell Y36:

  =IF($X36=0,"Poor",IF($X36=1,"Average",IF($X36=2,"Good","No data")))

N.B.

• For female's data use above shown array (CSE) formula, and remember to replace "M" with "F".
• Then calculate Points & Status using formula shown above.
• Adjust cell references in the formula as needed.

Solution 2:

For Excel 2010, you can use this formula for Male:

=(G2>=VALUE(LEFT(INDEX($C$3:$D$5,MATCH(F2,$A$3:$A$5,0),1),FIND("-",INDEX($C$3:$D$5,MATCH(F2,$A$3:$A$5,0),1))-1)))+(G2>=INDEX($C$3:$D$5,MATCH(F2,$A$3:$A$5,0),2))

where your criteria table is placed in A1, your pushups are placed in G2 and your age is placed in F2.

And this formula for female:

=(G9>=VALUE(LEFT(INDEX($C$9:$D$11,MATCH(F9,$A$9:$A$11,0),1),FIND("-",INDEX($C$9:$D$11,MATCH(F9,$A$9:$A$11,0),1))-1)))+(G9>=INDEX($C$9:$D$11,MATCH(F9,$A$9:$A$11,0),2))

where your criteria table is placed in A1, your pushups are placed in G9 and your age is placed in F9. Like this:

First, because you are giving points of 0, 1 and 2 for poor, average and good, you can ignore pushups below the minimum of "Average", so this formula simply does not look at that column of your criteria table.

Second, your formula can be reshaped to give points based on crossing each threshold value. e.g. the logic is:

1. If pushups is greater than or equal to 23, give one point.

2. If pushups is greater than or equal to 50, give another point.

This allows you to ignore the upper boundary of "Average" so the formula only has to parse out the lower boundary of "Average" using:

VALUE(LEFT(INDEX($C$3:$D$5,MATCH(F2,$A$3:$A$5,0),1),FIND("-",INDEX($C$3:$D$5,MATCH(F2,$A$3:$A$5,0),1))-1))

The INDEX($C$3:$D$5,MATCH(F2,$A$3:$A$5,0),1) selects which row to read in your criteria table using F2 as the age category by searching in column A for F2 and then telling the INDEX to look at that row for criteria which are in C3:D5. It then reads the LEFT side up to the "-" and then this is then turned into a number using VALUE.

Because the Good threshold is not text, you do not need all of these gymnastics, so you can do a simple: +(G2>=INDEX($C$3:$D$5,MATCH(F2,$A$3:$A$5,0),2)) to add the second point.

Lastly, unless you have to stay with your criteria table design you could make it a lot easier by using this:

and then your formula becomes this:

=SUM(--(H2>=INDEX($C$2:$D$7,MATCH(F2&G2,$A$2:$A$7&$B$2:$B$7,0),{1,2})))