Excel Question

[SrikanthL]

Recently my organization given a test to us, In that I got a question which I couldn't find the answer can anyone help me on this.

Conditions were:

If Handles were greater than 8 and efficiency is greater than 80 then "Great".

If Handles were lesser than 8 and efficiency is greater than 80 then "Fine".

If Handles were lesser than 8 and efficiency is lesser than 80 then "Not Eligible"

- We need to find for every login.

I don't understand how to solve this condition, I used If with nested condition, But I couldn't get the answer.

 

[bosco_yip]

What is the result: "If Handles is greater than 8 and efficiency is less than 80 ?"

Regards

 

[SrikanthL]

Sorry to forget!

#bosco_yip,

If Handles is greater than 8 and efficiency is less than 80 then "Not Eligible"

 

[bosco_yip]

Here is the formula in F5, copied down :

=IF((D5>=8)*(E5>=80),"Great",IF((D5<8)*(E5>=80),"Fine","Not Eligible"))

Conditions were:

If Handles is greater than & equal to 8 and efficiency is greater than & equal to 80, then "Great".

If Handles is less than 8 and efficiency is greater than & equal to 80, then "Fine".

Else "Not Eligible"

Regards
Bosco

 

[SrikanthL]

Thank you, It's really great. I appreciate your help

 

[Peter Bartholomew]

The conditions can be simplified if one considers the efficiency constraint first. The formula in row 5 would become
= IF(E5>80, IF(D5>=8,"Great","Fine"),"Not Eligible")

I tend to refactor such solutions to give more in the way of readability
= IF(Eligible?, IF(HighVol?,"Great","Fine"),"Not Eligible")
where 'Eligible?' and 'HighVol?' are the named formulas
= Sheet1!$E12>80
= Sheet1!$D13>=8
respectively.
This can be taken a step further on the legibility stakes by introducing a table and structured referencing
Eligible?: = Contacts[@Efficiency] > 80
HighVol?: = Contacts[@Handles] >= 8

It would be interesting to see what your organization makes of such a solution. Would the tester understand what is going on?