if statement with "&"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to compare two quarter grades to see if a student improved, stayed the
same or decreased. the first quarter is cell b3 and the second quarter is
cell b8.

if the first quarter is "s", and the second quarter is either "u" or "i"
then the student decreaed. If the first quarter is "u" and the second quarter
is "s" or "i" then the student improved. If the first quarter is "i" and the
second quarter is "s" then the student improved. If the first quarter is "i"
and the second quarter is "u" then the student decreased.

My if statement isn't working properly - see below. Any suggestions? thanks.

=IF(B3=B8,"same",IF(B3="s"&B8<>"s","declined", IF(B3="u"&B8<>"u",
"improved", IF(B3="i"&B8="s", "improved","down"))))
 
Look in HELP for the AND() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I want to compare two quarter grades to see if a student improved, stayed the
| same or decreased. the first quarter is cell b3 and the second quarter is
| cell b8.
|
| if the first quarter is "s", and the second quarter is either "u" or "i"
| then the student decreaed. If the first quarter is "u" and the second quarter
| is "s" or "i" then the student improved. If the first quarter is "i" and the
| second quarter is "s" then the student improved. If the first quarter is "i"
| and the second quarter is "u" then the student decreased.
|
| My if statement isn't working properly - see below. Any suggestions? thanks.
|
| =IF(B3=B8,"same",IF(B3="s"&B8<>"s","declined", IF(B3="u"&B8<>"u",
| "improved", IF(B3="i"&B8="s", "improved","down"))))
|
|
 
Rewrite to use AND(), except you don't need it

=IF(B3=B8,"same",IF(B3="s","declined",IF(B3="u","improved",
IF(B8="s","improved","declined"))))
 
try this

=IF(B3=B8,"SAME",IF(AND(B3="S",B8="S"),"DECLINED",IF(AND(B3="U",B8<>"U"),"IMPROVED",IF(AND(B3="I",B8="S"),"IMPROVED","DOWN"))))

Should work.
Thanks
Gary
 
small correction

=IF(B3=B8,"SAME",IF(AND(B3="S",B8<>"S"),"DECLINED",IF(AND(B3="U",B8<>"U"),"IMPROVED",IF(AND(B3="I",B8="S"),"IMPROVED","DOWN"))))
 
Joanie23 -

Your formula (and Gary's) is WAY TOO COMPLEX. Look at the one I gave you.
It gives precisely the answers you want, but is far simpler.
 
Back
Top