Hi all,

The following is the function that I currently have:

=IF(OR(M134<0,M135<0,M138<0,M139<0,M142<0,M14 3<0,M146<0,M147<0,M150<0,M151<0,M154<0,M155< 0,M158<0,M159<0,M162<0,M163<0,M166<0,M167<0 ,M170<0,M171<0,M174<0,M175<0,M178<0,M179<0), 1,0)

However I would like the function to be able to cover more than 30 OR
arguements, for example:

'=IF(OR(M134<0,M135<0,M138<0,M139<0,M142<0,M1 43<0,M146<0,M147<0,M150<0,M151<0,M154<0,M155 <0,M158<0,M159<0,M162<0,M163<0,M166<0,M167< 0,M170<0,M171<0,M174<0,M175<0,M178<0,M179<0, H134<0,H135<0,H138<0,H139<0,H142<0,H143<0,H1 46<0,H147<0,H150<0,H151<0,H154<0,H155<0,H158 <0,H159<0,H162<0,H163<0,H166<0,H167<0,H170< 0,H171<0,H174<0,H175<0,H178<0,H179<0),1,0)

Any ideas on how I could achieve this??

Thanks in advance.

Regards

James

Try...

=(SUMPRODUCT(--ISNUMBER(MATCH(MOD(ROW(H134:H179)-ROW(H134),4),{0,1},0)),(
H134:H179<0)+(M134:M179<0))0)+0

Hope this helps!

In article ,
James T wrote:

Hi all,

The following is the function that I currently have:

=IF(OR(M134<0,M135<0,M138<0,M139<0,M142<0,M14 3<0,M146<0,M147<0,M150<0
,M151<0,M154<0,M155<0,M158<0,M159<0,M162<0,M 163<0,M166<0,M167<0,M170<
0,M171<0,M174<0,M175<0,M178<0,M179<0),1,0)

However I would like the function to be able to cover more than 30 OR
arguements, for example:

'=IF(OR(M134<0,M135<0,M138<0,M139<0,M142<0,M1 43<0,M146<0,M147<0,M150<
0,M151<0,M154<0,M155<0,M158<0,M159<0,M162<0, M163<0,M166<0,M167<0,M170
<0,M171<0,M174<0,M175<0,M178<0,M179<0,H134< 0,H135<0,H138<0,H139<0,H1
42<0,H143<0,H146<0,H147<0,H150<0,H151<0,H154 <0,H155<0,H158<0,H159<0,
H162<0,H163<0,H166<0,H167<0,H170<0,H171<0,H1 74<0,H175<0,H178<0,H179<
0),1,0)

Any ideas on how I could achieve this??

Thanks in advance.

Regards

James

One way

=SUMPRODUCT(--(ROUND(MOD(ROW(M134:M500),4)/3,0)=1),--(M134:M500<0))

you can extend that as much as you want as long as you are using the same
patter with 2 pairs of cells checked, 2 not checked, 2 checked and so on


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"James T" wrote in message
...
Hi all,

The following is the function that I currently have:

=IF(OR(M134<0,M135<0,M138<0,M139<0,M142<0,M14 3<0,M146<0,M147<0,M150<0,M151<0,M154<0,M155< 0,M158<0,M159<0,M162<0,M163<0,M166<0,M167<0 ,M170<0,M171<0,M174<0,M175<0,M178<0,M179<0), 1,0)

However I would like the function to be able to cover more than 30 OR
arguements, for example:

'=IF(OR(M134<0,M135<0,M138<0,M139<0,M142<0,M1 43<0,M146<0,M147<0,M150<0,M151<0,M154<0,M155 <0,M158<0,M159<0,M162<0,M163<0,M166<0,M167< 0,M170<0,M171<0,M174<0,M175<0,M178<0,M179<0, H134<0,H135<0,H138<0,H139<0,H142<0,H143<0,H1 46<0,H147<0,H150<0,H151<0,H154<0,H155<0,H158 <0,H159<0,H162<0,H163<0,H166<0,H167<0,H170< 0,H171<0,H174<0,H175<0,H178<0,H179<0),1,0)

Any ideas on how I could achieve this??

Thanks in advance.

Regards

James

The following is the function that I currently have:

=IF(OR(M134<0,M135<0,M138<0,M139<0,M142<0,M14 3<0,M146<0,M147<0,M150<0,M151<0,M154<0,M155< 0,M158<0,M159<0,M162<0,M163<0,M166<0,M167<0 ,M170<0,M171<0,M174<0,M175<0,M178<0,M179<0), 1,0)

However I would like the function to be able to cover more than 30 OR
arguements, for example:

'=IF(OR(M134<0,M135<0,M138<0,M139<0,M142<0,M1 43<0,M146<0,M147<0,M150<0,M151<0,M154<0,M155 <0,M158<0,M159<0,M162<0,M163<0,M166<0,M167< 0,M170<0,M171<0,M174<0,M175<0,M178<0,M179<0, H134<0,H135<0,H138<0,H139<0,H142<0,H143<0,H1 46<0,H147<0,H150<0,H151<0,H154<0,H155<0,H158 <0,H159<0,H162<0,H163<0,H166<0,H167<0,H170< 0,H171<0,H174<0,H175<0,H178<0,H179<0),1,0)

Any ideas on how I could achieve this??


So if any of those cells are not equal to zero, you want a 1? And 0 if
none are not equal to zero? Why don't you just use:
=SUM(IF(M134:M179<0,1,0))

Interesting how you've used ROUND instead ISNUMBER/MATCH. Nice one, Peo!

In article ,
"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(ROUND(MOD(ROW(M134:M500),4)/3,0)=1),--(M134:M500<0))

you can extend that as much as you want as long as you are using the same
patter with 2 pairs of cells checked, 2 not checked, 2 checked and so on


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com