CODEWITHSUNDEEP
excellistrandomconditional-statementsformula
Nat Aes
Nat Aes

Reputation: 927

Random Item From a List Using Multiple Conditions

I use the following formula to output a random item from a list, conditional on certain factors. In this example, the student names are in column A and test scores are in column B. The condition for a random student to be chosen is that his or her test scores are more than 80%:

{=INDEX(Database!A1:A100,SMALL(IF(Database!B1:B100>=80%,ROW(Database!A1:A100)-MIN(ROW(Database!A1:A100))+1),RANDBETWEEN(1,COUNTIF(Database!B1:B100,">=80%"))))}

In addition to these columns, I also have level of education in column C (the options are Elementary, High School and College). How can I amend the above formula to output a random student conditional on a test score more than 80% AND a college education?

Upvotes: 0

Views: 2417

Answers (2)

XOR LX
XOR LX

Reputation: 7742

If you have Excel 2007 or later:

=INDEX(Database!A1:A100,SMALL(IF(Database!B1:B100>=80%,IF(Database!C1:C100="College",ROW(Database!A1:A100)-MIN(ROW(Database!A1:A100))+1)),RANDBETWEEN(1,COUNTIFS(Database!B1:B100,">=80%",Database!C1:C100,"College"))))

If not:

=INDEX(Database!A1:A100,SMALL(IF(Database!B1:B100>=80%,IF(Database!C1:C100="College",ROW(Database!A1:A100)-MIN(ROW(Database!A1:A100))+1)),RANDBETWEEN(1,SUM((Database!B1:B100>=80%)*(Database!C1:C100="College")))))

Both are still array formulas.

Regards

Upvotes: 2

barry houdini
barry houdini

Reputation: 46341

You can use this version

=INDEX(Database!A1:A100,SMALL(IF((Database!B1:B100>=80%)*(Database!C1:C100="College"),ROW(Database!A1:A100)-MIN(ROW(Database!A1:A100))+1),RANDBETWEEN(1,COUNTIFS(Database!B1:B100,">=80%",Database!C1:C100,"College"))))

formula needs to be confirmed with CTRL+SHIFT+ENTER

Extra criteria added to the IF function with SMALL and the same criteria added into a COUNTIFS function at the end (instead of COUNTIF)

Upvotes: 2

Related Questions