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)))

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!

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!

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 :) ?

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 © 2020 vBulletin Solutions Inc. All rights reserved.