software

Hi all,

The following is the function that I currently have:

=IF(OR(M134lt;gt;0,M135lt;gt;0,M138lt;gt;0,M139lt;gt;0,M142lt;gt;0,M14 3lt;gt;0,M146lt;gt;0,M147lt;gt;0,M150lt;gt;0,M151lt;gt;0,M154lt;gt;0,M155lt; gt;0,M158lt;gt;0,M159lt;gt;0,M162lt;gt;0,M163lt;gt;0,M166lt;gt;0,M167lt;gt;0 ,M170lt;gt;0,M171lt;gt;0,M174lt;gt;0,M175lt;gt;0,M178lt;gt;0,M179lt;gt;0), 1,0)

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

'=IF(OR(M134lt;gt;0,M135lt;gt;0,M138lt;gt;0,M139lt;gt;0,M142lt;gt;0,M1 43lt;gt;0,M146lt;gt;0,M147lt;gt;0,M150lt;gt;0,M151lt;gt;0,M154lt;gt;0,M155 lt;gt;0,M158lt;gt;0,M159lt;gt;0,M162lt;gt;0,M163lt;gt;0,M166lt;gt;0,M167lt;gt; 0,M170lt;gt;0,M171lt;gt;0,M174lt;gt;0,M175lt;gt;0,M178lt;gt;0,M179lt;gt;0, H134lt;gt;0,H135lt;gt;0,H138lt;gt;0,H139lt;gt;0,H142lt;gt;0,H143lt;gt;0,H1 46lt;gt;0,H147lt;gt;0,H150lt;gt;0,H151lt;gt;0,H154lt;gt;0,H155lt;gt;0,H158 lt;gt;0,H159lt;gt;0,H162lt;gt;0,H163lt;gt;0,H166lt;gt;0,H167lt;gt;0,H170lt;gt; 0,H171lt;gt;0,H174lt;gt;0,H175lt;gt;0,H178lt;gt;0,H179lt;gt;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:H179lt;gt;0) (M134:M179lt;gt;0))gt;0) 0

Hope this helps!

In article gt;,
James T gt; wrote:

gt; Hi all,
gt;
gt; The following is the function that I currently have:
gt;
gt; =IF(OR(M134lt;gt;0,M135lt;gt;0,M138lt;gt;0,M139lt;gt;0,M142lt;gt;0,M14 3lt;gt;0,M146lt;gt;0,M147lt;gt;0,M150lt;gt;0
gt; ,M151lt;gt;0,M154lt;gt;0,M155lt;gt;0,M158lt;gt;0,M159lt;gt;0,M162lt;gt;0,M 163lt;gt;0,M166lt;gt;0,M167lt;gt;0,M170lt;
gt; gt;0,M171lt;gt;0,M174lt;gt;0,M175lt;gt;0,M178lt;gt;0,M179lt;gt;0),1,0)
gt;
gt; However I would like the function to be able to cover more than 30 OR
gt; arguements, for example:
gt;
gt; '=IF(OR(M134lt;gt;0,M135lt;gt;0,M138lt;gt;0,M139lt;gt;0,M142lt;gt;0,M1 43lt;gt;0,M146lt;gt;0,M147lt;gt;0,M150lt;gt;
gt; 0,M151lt;gt;0,M154lt;gt;0,M155lt;gt;0,M158lt;gt;0,M159lt;gt;0,M162lt;gt;0, M163lt;gt;0,M166lt;gt;0,M167lt;gt;0,M170
gt; lt;gt;0,M171lt;gt;0,M174lt;gt;0,M175lt;gt;0,M178lt;gt;0,M179lt;gt;0,H134lt;gt; 0,H135lt;gt;0,H138lt;gt;0,H139lt;gt;0,H1
gt; 42lt;gt;0,H143lt;gt;0,H146lt;gt;0,H147lt;gt;0,H150lt;gt;0,H151lt;gt;0,H154 lt;gt;0,H155lt;gt;0,H158lt;gt;0,H159lt;gt;0,
gt; H162lt;gt;0,H163lt;gt;0,H166lt;gt;0,H167lt;gt;0,H170lt;gt;0,H171lt;gt;0,H1 74lt;gt;0,H175lt;gt;0,H178lt;gt;0,H179lt;gt;
gt; 0),1,0)
gt;
gt; Any ideas on how I could achieve this??
gt;
gt; Thanks in advance.
gt;
gt; Regards
gt;
gt; James

One way

=SUMPRODUCT(--(ROUND(MOD(ROW(M134:M500),4)/3,0)=1),--(M134:M500lt;gt;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

nwexcelsolutions.com
quot;James Tquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; The following is the function that I currently have:
gt;
gt; =IF(OR(M134lt;gt;0,M135lt;gt;0,M138lt;gt;0,M139lt;gt;0,M142lt;gt;0,M14 3lt;gt;0,M146lt;gt;0,M147lt;gt;0,M150lt;gt;0,M151lt;gt;0,M154lt;gt;0,M155lt; gt;0,M158lt;gt;0,M159lt;gt;0,M162lt;gt;0,M163lt;gt;0,M166lt;gt;0,M167lt;gt;0 ,M170lt;gt;0,M171lt;gt;0,M174lt;gt;0,M175lt;gt;0,M178lt;gt;0,M179lt;gt;0), 1,0)
gt;
gt; However I would like the function to be able to cover more than 30 OR
gt; arguements, for example:
gt;
gt; '=IF(OR(M134lt;gt;0,M135lt;gt;0,M138lt;gt;0,M139lt;gt;0,M142lt;gt;0,M1 43lt;gt;0,M146lt;gt;0,M147lt;gt;0,M150lt;gt;0,M151lt;gt;0,M154lt;gt;0,M155 lt;gt;0,M158lt;gt;0,M159lt;gt;0,M162lt;gt;0,M163lt;gt;0,M166lt;gt;0,M167lt;gt; 0,M170lt;gt;0,M171lt;gt;0,M174lt;gt;0,M175lt;gt;0,M178lt;gt;0,M179lt;gt;0, H134lt;gt;0,H135lt;gt;0,H138lt;gt;0,H139lt;gt;0,H142lt;gt;0,H143lt;gt;0,H1 46lt;gt;0,H147lt;gt;0,H150lt;gt;0,H151lt;gt;0,H154lt;gt;0,H155lt;gt;0,H158 lt;gt;0,H159lt;gt;0,H162lt;gt;0,H163lt;gt;0,H166lt;gt;0,H167lt;gt;0,H170lt;gt; 0,H171lt;gt;0,H174lt;gt;0,H175lt;gt;0,H178lt;gt;0,H179lt;gt;0),1,0)
gt;
gt; Any ideas on how I could achieve this??
gt;
gt; Thanks in advance.
gt;
gt; Regards
gt;
gt; James
gt; The following is the function that I currently have:
gt;
gt; =IF(OR(M134lt;gt;0,M135lt;gt;0,M138lt;gt;0,M139lt;gt;0,M142lt;gt;0,M14 3lt;gt;0,M146lt;gt;0,M147lt;gt;0,M150lt;gt;0,M151lt;gt;0,M154lt;gt;0,M155lt; gt;0,M158lt;gt;0,M159lt;gt;0,M162lt;gt;0,M163lt;gt;0,M166lt;gt;0,M167lt;gt;0 ,M170lt;gt;0,M171lt;gt;0,M174lt;gt;0,M175lt;gt;0,M178lt;gt;0,M179lt;gt;0), 1,0)
gt;
gt; However I would like the function to be able to cover more than 30 OR
gt; arguements, for example:
gt;
gt; '=IF(OR(M134lt;gt;0,M135lt;gt;0,M138lt;gt;0,M139lt;gt;0,M142lt;gt;0,M1 43lt;gt;0,M146lt;gt;0,M147lt;gt;0,M150lt;gt;0,M151lt;gt;0,M154lt;gt;0,M155 lt;gt;0,M158lt;gt;0,M159lt;gt;0,M162lt;gt;0,M163lt;gt;0,M166lt;gt;0,M167lt;gt; 0,M170lt;gt;0,M171lt;gt;0,M174lt;gt;0,M175lt;gt;0,M178lt;gt;0,M179lt;gt;0, H134lt;gt;0,H135lt;gt;0,H138lt;gt;0,H139lt;gt;0,H142lt;gt;0,H143lt;gt;0,H1 46lt;gt;0,H147lt;gt;0,H150lt;gt;0,H151lt;gt;0,H154lt;gt;0,H155lt;gt;0,H158 lt;gt;0,H159lt;gt;0,H162lt;gt;0,H163lt;gt;0,H166lt;gt;0,H167lt;gt;0,H170lt;gt; 0,H171lt;gt;0,H174lt;gt;0,H175lt;gt;0,H178lt;gt;0,H179lt;gt;0),1,0)
gt;
gt; Any ideas on how I could achieve this??
gt;

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:M179lt;gt;0,1,0))Interesting how you've used ROUND instead ISNUMBER/MATCH. Nice one, Peo!

In article gt;,
quot;Peo Sjoblomquot; gt; wrote:

gt; One way
gt;
gt; =SUMPRODUCT(--(ROUND(MOD(ROW(M134:M500),4)/3,0)=1),--(M134:M500lt;gt;0))
gt;
gt; you can extend that as much as you want as long as you are using the same
gt; patter with 2 pairs of cells checked, 2 not checked, 2 checked and so on
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com