Nested If with comparison operators

[SG]

Hi Experts,

I'm trying to calculate the score on basis of a range table. For this, i have used the nested if statement however, i'm unable to get the accurate result. Please help me on this. I have attached the file for your reference.

 

[NARAYANK991]

Hi ,

Use this : =IF(B13<=3,1,IF(B13<=8,2,IF(B13<=12,3,IF(B13<=16,4,5))))

If you wish to use the OR or AND operators , your conditions must be written as :

AND(B13>=3,B13<=8)

but in this case it is not necessary.

Narayan

 

[kchiba]

Hi,

Look at the attached file, it uses a Named Range and the Vlookup function.

kanti

 

[SG]

Thanks Narayan sir & Kchiba sir. I have a query to Kchiba, only one problem is coming is that when the value is 16, it gives the score of 5 whereas it should give 4. please look in to this & also clear my one more query for vlookup that vlookup only looks for the specific value whereas in this case, it's also giving the data between the ranges.Please explain & help with the problem.

 

[Faseeh]

@SG

Another way.

=LOOKUP(D13,{0,4,9,13,17},$B$4:$B$8)

...in C1 and drag down.

Edit: This one is giving 4 at 16 i guess. Can you try it?

 

[SG]

ohh yes... it works. Can you please explain this.i know you have used array but I never got this Lookup formula.

 

[Faseeh]

Hi SG,

According to excel help: ".....it matches the largest value in lookup_vector that is less than or equal to lookup_value." so you have to setup formula accordingly. The value you are looking for in looked into the array {0,4,9,13,17}, so when you, lets say look for 2, the largest value smaller then or equal to it is 0, that return the first value from (1,2,3,4,5}, had you looked for 10, it would have returned 9, that in turn will give 3 as a result. Hope this explanation helps.

 

[SG]

Thnks Faseeh

 

[kchiba]

Thanks for picking this up Fasseh