Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012
    Posts
    12

    Lightbulb 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

  2. #2
    Join Date
    Oct 2012
    Posts
    12
    Quote Originally Posted by 44hjk44 View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •