Counting & Conditions

[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

 

[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

 

[Khalil Handal]

I'll try it ! thank you

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

 

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

 

[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

 

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

 

[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

 

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

 

[Khalil Handal]

Yes it does. Thank you Bob.

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)

 

[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

 

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

 

[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

 

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