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)))
Bob Phillips
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!
Bob Phillips
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!
Bob Phillips
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 :) ?
Bob Phillips
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
Isn't wrong :) ..It's ok .:) Thanks for your answers!
Have a nice day!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.