New Contributor

I am taking an Excell class and I have the problem below to solve. I am currently using this formula to put in the boxes men<5'08" with purple shirts, men >5'08" with pink shirts. I am trying to add females with green shirts but I get value # when I add the female formula to my current formula.

=IF(AND(D10>508,C10="male"),"pink","purple")...Any help is appreciated....Thanks

Question to solve:

Using the IF(AND(function, write a formula beginning in cell F10 (again, Jackson, S) that tests players by both height and gender in order to return the answer you're needing. In this case disregard the "key" as we're only dealing with two colors, Green and Purple. You need to test

whether or not the value in the height column is less than or equal to 5'8" (508), AND whether or not the value in the gender column is "Male" or "Female."

The objective is to assign females 5'8" or less with a Green t-shirt, and males 5'8" or less with a Purple t-shirt. You'll want any players who are taller than 5'8" to show up as "Pink".

2 Replies

@mhanna807 I'm going to let you figure out the exact formula yourself, but when you read the question carefully, all players >508 are pink. So you don't need to test for >508 AND gender.

The solution thus needs to test for:

if <=508 AND male, then purple, else if <= 508 AND female, then green, otherwise pink.

A working formula, (but not in line with the specific instructions as it doesn't include AND), would be:

=IF(D10>508,"pink",IF(C10="male","purple","green"))

assuming that the gender will always be male or female, i.e. no blanks.

This one first checks the hight. Everyone >508 gets pink. Then checks for either of the gender types (male or female) and sets the color for true and for false, as all of these players will be <=508. no need to check that again. So you could also check for female and swap the colors. Like this:

=IF(D10>508,"pink",IF(C10="female","green","purple))