Problem with If function and min range.

Hey,

I am facing a problem with an if function. I calculated the average grade for every student and now i am facing the following problem:

Display "Very Bad" if grade is below 3, display "Bad" from 3 till 5, display "Average" from 5 till 7, display "Nice" from 7 till 9 and display "Very Nice" for 9 till 10. However if a student has scored a 9 or higher for all exercises and the final exercise then performance should be displayed as "Top Student"

I know how to do the first step:

=IF(F2<3;"Very Bady";IF(F2>=3;"Bad";IF(F2>=5;"Average";IF(F2>=7;"Nice";"Very Nice"))))


BUT I dont know how to solve the if function including the Top Student part. I tried it like this. Can anyone help me how to get formular work?


=IF(F2<3;"Very Bad";IF(F2>=3;"Bad";IF(F2>=5;"Average";IF(F2>=7;"Nice";"Very Nice";IF(MIN(B2:E2)>=9);”Top Student”; "")))))


Thanks for your help in advance.

Trotto

You need to put your top student test first, like this:

=IF(MIN(B2:E2)>=9);”Top Student”;IF(F2<3;"Very Bad";IF(F2<5;"Bad";IF(F2<7;"Average";IF(F2<9;"Nice";"Very Nice")))))

I've also changed the comparisons, for consistency.

Hope this helps.

Pete

Hey,

thanks for your help. Unfortuntley I still get an error for this formular. I really have no idea what is wrong in this formular.. F2 is always the average of the grades (B2:E2) and Top student should only be displayed if every grade between B2 and E2 is bigger or equal than 9.

Sorry, I left a bracket in when I moved the formula around. Try this:

=IF(MIN(B2:E2)>=9;”Top Student”;IF(F2<3;"Very Bad";IF(F2<5;"Bad";IF(F2<7;"Average";IF(F2<9;"Nice";"Very Nice")))))

Hope this helps.

Pete

Thank you very much, it works!

Can you please explain what you meant with: "I've also changed the comparisons, for consistency."

Trotto

Originally Posted by tratto

Can you please explain what you meant with: "I've also changed the comparisons, for consistency."

Well you had these terms in your IFs:

F2<3 , F2>=3 , F2>=5 , F2>=7

whereas I made all mine less than the number for that range:

F2<3 , F2<5 , F2<7 , F2<9

which makes the logic stand out more clearly.

Hope this helps.

Pete

Hey, thanks for the explaination.

I am facing another problem and i dont know which function to use. The problem stated like this:if 2 out of 3 grades for grade average, letter and interview are lower than 6 then display "Sorry, work harder at this university"

Do I have to use the CountIf formular or is there another way to solve this problem?

regards

Something like this:

=IF(COUNTIF(range;"<6")>=2;"work harder";"satisfactory")

Hope this helps.

Pete