Formula too long - help needed!

[sadsfan]

Hi, I have written the formula, but Excel 2003 says it is too long, could anyone out there help me shorten it?

[FONT=Times New Roman][SIZE=3]=OR(AND(CG5=1,OR(AM5={19,21,23,25,27,29,31,33,35})),AND[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman](CG5=2,OR(AM5={25,27,29,31,33,35})),AND(CG5=3,OR(AM5=[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]{31,33,35})),AND(CG5=1,OR(AS5={19,21,23,25,27,29,31,33,35})),AND
(CG5=2,OR(AS5={25,27,29,31,33,35})),AND(CG5=3,OR(AS5=
{31,33,35})),AND(CG5=1,OR(AV5={19,21,23,25,27,29,31,33,35})),AND
(CG5=2,OR(AV5={25,27,29,31,33,35})),AND(CG5=3,OR(AV5=
{31,33,35})),AND(CG5=1,OR(AY5={19,21,23,25,27,29,31,33,35})),AND
(CG5=2,OR(AY5={25,27,29,31,33,35})),AND(CG5=3,OR(AY5=
{31,33,35})),AND(CG5=1,OR(BE5={19,21,23,25,27,29,31,33,35})),AND
(CG5=2,OR(BE5={25,27,29,31,33,35})),AND(CG5=3,OR(BE5=
{31,33,35})),AND(CG5=1,OR(BH5={19,21,23,25,27,29,31,33,35})),AND
(CG5=2,OR(BH5={25,27,29,31,33,35})),AND(CG5=3,OR(BH5=
{31,33,35})),AND(CG5=1,OR(BK5={19,21,23,25,27,29,31,33,35})),AND
(CG5=2,OR(BK5={25,27,29,31,33,35})),AND(CG5=3,OR(BK5=
{31,33,35})),AND(CG5=1,OR(BQ5={19,21,23,25,27,29,31,33,35})),AND
(CG5=2,OR(BQ5={25,27,29,31,33,35})),AND(CG5=3,OR(BQ5=
{31,33,35})),AND(CG5=1,OR(BT5={19,21,23,25,27,29,31,33,35})),AND
(CG5=2,OR(BT5={25,27,29,31,33,35})),AND(CG5=3,OR(BT5=
{31,33,35})))[/FONT][/SIZE]

Hope that makes sense, I've pasted it as code, but it does go in the formula bar.

Thanks

 

[Oaktree]

If you have the morefunc.dll add-in, try:

=OR(ARRAY.JOIN(AM5,AS5,AV5,AY5,BE5,BH5,BK5,BQ5,BT5)=CHOOSE(CG5,{19,21,23,25,27,29,31,33,35},{25,27,29,31,33,35},{31,33,35}))

Which is an array formula and must be confirmed with CTRL+SHIFT+ENTER

 

[sadsfan]

If you have the morefunc.dll add-in, try:

=OR(ARRAY.JOIN(AM5,AS5,AV5,AY5,BE5,BH5,BK5,BQ5,BT5)=CHOOSE(CG5,{19,21,23,25,27,29,31,33,35},{25,27,29,31,33,35},{31,33,35}))

Which is an array formula and must be confirmed with CTRL+SHIFT+ENTER

I have the morefunc add-in, but not all my users have that add-in. Is there any other way? Thanks

 

[Oaktree]

You could always embed morefunc into your file.

Alternatively, you could break them out =OR(AM5 = CHOOSE(...), AS5 = CHOOSE(..))

Envoking the CHOOSE function alone should make it fit. Less elegant than with the ARRAY.JOIN though.

 

[sadsfan]

You could always embed morefunc into your file.

Alternatively, you could break them out =OR(AM5 = CHOOSE(...), AS5 = CHOOSE(..))

Envoking the CHOOSE function alone should make it fit. Less elegant than with the ARRAY.JOIN though.

Thanks, how would I embed morefunc into the file? Or alternatively what would go in the brackets after the choose part?

 

[shg]

Maybe ...

= OR(CHOOSE({1;2;3;4;5;6;7;8;9},AM5,AS5,AV5,AY5,BE5,BH5,BK5,BQ5,BT5)
= CHOOSE(CG5, {19,21,23,25,27,29,31,33,35}, {25,27,29,31,33,35}, {31,33,35}))

... confirmed with Ctrl+Shift+Enter

 

[wigi]

Using just normal Excel functionality and no matrix formula:

- make a range of cells (called rng for instance), containing the values (downwards as in a 'column'): 35, then 33, then 31, and so on, until 19

- use this formula:

=AND(CG5<3,OR(ISNUMBER(MATCH(AM5,OFFSET(rng,,,(4-CG5)*3),0))))

The second part in the AND formula will need to be copy-pasted for the other cells - here I only did AM5 (it's in bold).

Nice formula Stephen

Wigi

 

[shg]

YTY, Wigi, yours too, and OakTree's.

 

[Oaktree]

Thanks, how would I embed morefunc into the file?

There should be an option in tools --> morefunc --> "embed morefunc in the workbook..."

 

[shg]

Oaktree, does that work in Excel 2007+?