Is there a shorter formula for this pls, help!

[mozzaroo]

hi, is there any other way I can make the following equation any shorter please?


=AND(OR(B201=3,B201=6,B201=9,B201=12,B201=15,B201=18,B201=21,B201=24,B201=27,B201=30,B201=33,B201=36), OR(B202=3,B202=6,B202=9,B202=12,B202=15,B202=18,B202=21,B202=24,B202=27,B202=30,B202=33,B202=36), OR(B203=3,B203=6,B203=9,B203=12,B203=15,B203=18,B203=21,B203=24,B203=27,B203=30,B203=33,B203=36), OR(B204=3,B204=6,B204=9,B204=12,B204=15,B204=18,B204=21,B204=24,B204=27,B204=30,B204=33,B204=36), OR(B205=3,B205=6,B205=9,B205=12,B205=15,B205=18,B205=21,B205=24,B205=27,B205=30,B205=33,B205=36), OR(B206=3,B206=6,B206=9,B206=12,B206=15,B206=18,B206=21,B206=24,B206=27,B206=30,B206=33,B206=36))

I have this in a cell as a conditional formatting to turn green when all of cells B201, B202, B203, B204, B205 and B206 have numbers 3,6,12,15,18,21,24,27,30,33, or 36 enetered in them.

I have to input this a range of different cells, and alter each one according so v. time consuming, so just wondered if theres a short formula that would do the same thing available? thanks guys!

 

[Jonmo1]

Welcome to the board...

Try

=SUMPRODUCT(--(ISNUMBER(MATCH({3,6,9,12,15,18,21,24,27,30,33,36},B201:B206,0))))=6

 

[Jonmo1]

Or instead of hardcoding the numbers in the formula..

=SUMPRODUCT(--(ISNUMBER(MATCH(F201:F211,B201:B206,0))))=6

F201:F211 = 3,6,9,etc..

 

[jasonb75]

I have to input this a range of different cells, and alter each one according

If your values are in a consistant pattern like your example, you could use something like

=SUMPRODUCT(--ISNUMBER(MATCH(B201:B206/3,ROW(1:12),0)))

 

[mozzaroo]

Hi
thanks for the replies, but can't quite seem to get it working, I inputed:


=SUMPRODUCT(--(ISNUMBER(MATCH({3,6,9,12,15,18,21,24,27,30,33,36},B201:B206,0))))=6</pre>
into conditional formatting but it says "You may not use unions, intersections, or array constants for Conditional Formatting criteria"

I have also tried
=SUMPRODUCT(--(ISNUMBER(MATCH(F201:F211,B201:B206,0))))=6
but had no luck with it, any more suggestions or alterations to them pls!

 

[Jonmo1]

Ahh, yes. Forgot that you can't use {...} in Conditional formatting.

For my second suggestion to work, you have to put

3 in F201
6 in F202
9 in F203
etc..

Try Jason's formula, it's quite elegant.

Hi
thanks for the replies, but can't quite seem to get it working, I inputed:



=SUMPRODUCT(--(ISNUMBER(MATCH({3,6,9,12,15,18,21,24,27,30,33,36},B201:B206,0))))=6


</PRE>
into conditional formatting but it says "You may not use unions, intersections, or array constants for Conditional Formatting criteria"

I have also tried
=SUMPRODUCT(--(ISNUMBER(MATCH(F201:F211,B201:B206,0))))=6
but had no luck with it, any more suggestions or alterations to them pls!

 

[Jonmo1]

Although, for Jason's to work, you''ll have to add the =6 to the end..

=SUMPRODUCT(--ISNUMBER(MATCH(B201:B206/3,ROW(1:12),0)))=6

 

[mozzaroo]

Excellent, thanks lot for the mega quick replies and the formulae! is going to save at least 15 hours off my spreadsheet, thanks !

 

[Ruddles]

On the basis that the numbers are all divisible by three and run from 3 to 36:-

=AND(
mod(B201,3)=0,B201>=3,B201<=36, 
mod(B202,3)=0,B202>=3,B202<=36, 
mod(B203,3)=0,B203>=3,B203<=36, 
mod(B204,3)=0,B204>=3,B204<=36, 
mod(B205,3)=0,B205>=3,B205<=36, 
mod(B206,3)=0,B206>=3,B206<=36
)

(I'm hopeless at array formulae!)