PDA

View Full Version : Solved: nasted if/sumproduct help

neditheg
04-02-2012, 12:49 AM
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
04-02-2012, 01:03 AM
Untested but try the following:

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

xld
04-02-2012, 01:14 AM
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
04-02-2012, 01:24 AM
:) a very simple solution!thanks! :)

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

thanks!

xld
04-02-2012, 01:54 AM
Sure.

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

neditheg
04-02-2012, 02:13 AM
TY!

neditheg
04-02-2012, 02:22 AM
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!

xld
04-02-2012, 02:37 AM
I am not sure I understand your question, but I don't think it would be an appropriate problem for SUMPRODUCT.

neditheg
04-02-2012, 04:20 AM
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 :) ?

xld
04-02-2012, 04:23 AM
Are you saying it is wrong and you don't know why?

neditheg
04-02-2012, 05:19 AM