If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 > Using multiple IF statements to do the SCORING

 44hjk44 Registered User Join Date: Oct 2012 Posts: 12
Using multiple IF statements to do the SCORING

 I have to do the scoring of a list, giving the scores from "Minus Two -2" to the "Plus Two 2" based on the criteria given below - Scoring Criteria - If > 4 ------------------------------ 2 If > 2 and < 4 ---------------------- 1 If < 2 and > - 2 -------------------- 0 If < - 2 and > - 4 ------------------ -1 If < - 4 ---------------------------- -2 I do not want to do this through Vlookup method, instead I prefer using the nested IF statements. But somehow I am not being able to get it right. Could someone please suggest a solution to this. Thanks
 44hjk44 Registered User Join Date: Oct 2012 Posts: 12
Quote:
 Originally Posted by 44hjk44 I have to do the scoring of a list, giving the scores from "Minus Two -2" to the "Plus Two 2" based on the criteria given below - Scoring Criteria - If > 4 ------------------------------ 2 If > 2 and < 4 ---------------------- 1 If < 2 and > - 2 -------------------- 0 If < - 2 and > - 4 ------------------ -1 If < - 4 ---------------------------- -2 I do not want to do this through Vlookup method, instead I prefer using the nested IF statements. But somehow I am not being able to get it right. Could someone please suggest a solution to this. Thanks
Found solution on another forum. The correct formula is -

=IF(A2>4,2,IF(AND(A2>2,A2<4),1,IF(AND(A2<2,A2>(-2)),0,IF(AND(A2<-2,A2>-4),-1,IF(A2<-4,-2,A2)))))

or

=IF(A2>=4,2,IF(A2>=2,1,IF(A2>=-2,0,IF(A2>=-4,-1,-2))))

Thanks