Counting & Conditions

K

Khalil Handal

Hi to evrybody,
I have 13 cells in the same row as follows:
J14, M14, P14, S14, V14, Y14, AB14, AE14, AH14, AK14, AN14, AQ14, AT14
Each of them contains a number which is a result from a calculation.

In Cell BD14, I want a value depending on the number of cells with the
following condition (counting):

If any 4 or more of the 13 cells has a number < 50 ---> put "Very Slow" in
cell DB14
If none of the cells has number < 50 ---> put "Very Fast" in cell BD14
If LESS than 4 cells has number < 50 ---> put "Moderate" in cell BD14

Any suggestions
 
K

KC Rippstein

Probably not a "power user" solution, but what the heck.
In BF14, put =J14, in BG14 =M14, and so on...this gives the data you want in
one contiguous range.
In BE14, put =COUNTIF(BF14:BR14,"<50")
In BD14, put =IF(BE14>3,"Very Slow",IF(BE14=0,"Very
Fast",IF(OR(BE14=1,BE14=2,BE14=3),"Moderate",NA())))
This will allow you to use this structure on any row.
-KC
 
K

Khalil Handal

I'll try it ! thank you

KC Rippstein said:
Probably not a "power user" solution, but what the heck.
In BF14, put =J14, in BG14 =M14, and so on...this gives the data you want
in one contiguous range.
In BE14, put =COUNTIF(BF14:BR14,"<50")
In BD14, put =IF(BE14>3,"Very Slow",IF(BE14=0,"Very
Fast",IF(OR(BE14=1,BE14=2,BE14=3),"Moderate",NA())))
This will allow you to use this structure on any row.
-KC
Click to expand...
 
B

Bob Phillips

=IF(SUMPRODUCT(--(MOD(COLUMN(J14:AT14),4)=2),--(J14:AT14<>""),--(J14:AT14<50))=0,"Very
Fast",
IF(SUMPRODUCT(--(MOD(COLUMN(J14:AT14),4)=2),--(J14:AT14<>""),--(J14:AT14<50))<4,"Moderate","Very
Slow"))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
K

Khalil Handal

thanks a lot it worked fine.
Is it possible to adjust it so as nothing is shown in the cells if any of
the mentioned cells is blank (no values), the range was:
J14, M14, P14, S14, V14, Y14, AB14, AE14, AH14, AK14, AN14, AQ14, AT14
Thanks
 
B

Bob Phillips

Do you mean

=IF(SUMPRODUCT(--(MOD(COLUMN(J14:AT14),4)=2),--(J14:AT14<>""),--(J14:AT14<50))=0,"",
IF(SUMPRODUCT(--(MOD(COLUMN(J14:AT14),4)=2),--(J14:AT14<>""),--(J14:AT14<50))<4,"Moderate","Very
Slow"))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
K

Khalil Handal

Hi Bob,
I want to keep "very Fast";
The values are:
1- very fast if none of the values in cells is < 50
2- moderate if 1 , 2 , or 3 numbers only < 50
3- very slow if 4 or greater are < han 50
4- "" if any of the cells mentioned is empty .

Hope it is clear now

Khalil
 
B

Bob Phillips

Does this do it?

=IF(SUMPRODUCT(--(MOD(COLUMN(J14:AT14),4)=2),--(J14:AT14=""))>0,"",
IF(SUMPRODUCT(--(MOD(COLUMN(J14:AT14),4)=2),--(J14:AT14<>""),--(J14:AT14<50))=0,"Very
Fast",
IF(SUMPRODUCT(--(MOD(COLUMN(J14:AT14),4)=2),--(J14:AT14<>""),--(J14:AT14<50))<4,"Moderate","Very
Slow")))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
K

Khalil Handal

Yes it does. Thank you Bob.


Bob Phillips said:
Does this do it?

=IF(SUMPRODUCT(--(MOD(COLUMN(J14:AT14),4)=2),--(J14:AT14=""))>0,"",
IF(SUMPRODUCT(--(MOD(COLUMN(J14:AT14),4)=2),--(J14:AT14<>""),--(J14:AT14<50))=0,"Very
Fast",
IF(SUMPRODUCT(--(MOD(COLUMN(J14:AT14),4)=2),--(J14:AT14<>""),--(J14:AT14<50))<4,"Moderate","Very
Slow")))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)
Click to expand...
 
K

Khalil Handal

Sorry to disturbe you again,
The formula is not giving very slow when more than 4 values are < 50.
I'am not sure if I miss something or not!
Khalil
 
B

Bob Phillips

Khalil,

It does for me, 4, 5 or more < 50 all give Very Slow.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
B

Bill Kuunders

As per usual...
excellent formula Bob.
Somehow you may have overlooked the intervals between the column numbers

MOD(COLUMN(J14:AT14),4)=2

should be MOD(COLUMN(J14:AT14),3)=1
to check every 3 rd column starting with 10, 13 ,16 etc
 
B

Bob Phillips

Bill identified the problem Khalil

=IF(SUMPRODUCT(--(MOD(COLUMN(J14:AT14),3)=1),--(J14:AT14=""))>0,"",
IF(SUMPRODUCT(--(MOD(COLUMN(J14:AT14),3)=1),--(J14:AT14<>""),--(J14:AT14<50))=0,"Very
Fast",
IF(SUMPRODUCT(--(MOD(COLUMN(J14:AT14),3)=1),--(J14:AT14<>""),--(J14:AT14<50))<4,"Moderate","Very
Slow")))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Top