Solved: nasted if/sumproduct help

[neditheg]

hello,

I need help with a formula.
I've attached an example.
In column K I want to get 1 if j2 between 40.1% and 60% , 2 if j2 between 60.1% and 85% , 3 if j2 is between 85.1% and 100% ...else, if j2<40% j2 must be 0.

Thanks!

[Aussiebear]

Untested but try the following:

=If(K2>80.1,3,If(K2>60.1,2,If(K2>40.1,1,0)))

[Bob Phillips]

A couple of ways

=IF(J2<=40%,0,IF(J2<=60%,1,IF(J2<=85%,2,3)))

or

=(J2>40%)+(J2>60%)+(J2>85%)

[neditheg]

:) a very simple solution!thanks! :)

xld ..if you appear ..can you put a sumproduct formula to solve my case?

thanks!

[Bob Phillips]

Sure.

=SUMPRODUCT(--(J2>{0.4,0.6,0.85}))

[neditheg]

TY!

[neditheg]

I'm trying to understand why I get the good result using this sumproduct statement. I've extended ur formula...

=SUMPRODUCT(--(J2>{0.4,0.6,0.85,0.95})) --> and I get 4 for cases when j2 > 95% ..but how does this sumproduct look when I need to get "apple" for j between 40% and 60% , "plum" for cases when j betwwen 60% and 85% and "treasure" for cases when j>85% ? :) thanks!

[Bob Phillips]

I am not sure I understand your question, but I don't think it would be an appropriate problem for SUMPRODUCT.

[neditheg]

Hmm ..I guess if I use

=SUMPRODUCT(--(J2>{0.1,0.2,0.5,0.6,0.7,0.9,0.95}))

for j between 0.1 and 0,2 I'll get 1
for j between 0.2 and 0.5 I'll get 2
for j between 0.5 and 0.6 I'll get 3
for j between 0.6 and 0.7 I'll get 4
for j between 0.7 and 0.9 I'll get 5
for j between 0.9 and 0.95 I'll get 6
and for j greater than 0.95 I'll get 7

and I'm asking why :) ?

[Bob Phillips]

Are you saying it is wrong and you don't know why?

[neditheg]

Isn't wrong :) ..It's ok .:) Thanks for your answers!

Have a nice day!