Can be here some changes for a faster speed ?

[ytayta555]

A good day everybody

I am looking for some changes in a formula , to get best speed for
calculate
it by excel application .
My first formula was :
=AND(COUNT(B1;B3;B5)<=1;COUNT(C1;C3;C3)<=1;COUNT(D1;D3;D5)<=1;COUNT(E1;E3;E­
5)<=1;COUNT(F1;F3;F5)<=1)
I was searching for an equivalent for this formula , and , thanks to
mr. Harlan Grove , I get the formula :
=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0,ISNUMBER(B1:F5)*{1;0;1;0;1})<=1)
in US formula sintax and in my locale sintax :
=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*{1|0|1|0|1})<=1)
and another :
=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0; ISNUMBER(B1:F5)*MOD(ROW(B1:F5);
2))<=1)
(this are array formulas ,must introduce with Ctrl-Shift-Enter)
For a better overview plase read and here :http://groups.google.ro/
group/microsoft.public.excel.worksheet.functions/browse_thread/thread/
25ce7b613b0a000b/5b152af7d8488120?hl=ro&lnk=gst&q=OLD
+Problem#5b152af7d8488120
_________________________________________________________________

for formula : =AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*{1|0|
1|0|1})<=1)
is possible to make some changes in it ,
maybe I'll can to transform it in a NON-array and NON-volatile
formula, or to change some function in it to work faster ?

Can this function to be an
equivalent :=AND(COUNT(MATCH(ROWS(B1:F5);TRANSPOSE({1;3;5}))>=1)) ??
It doesn't work still for me ...Please read and here , I'm not very
experimentate in functions and formulas : http://www.decisionmodels.com/optspeedj.htm
Please very much for help

Thanks so much for your time and kinlyness

 

[Rick Rothstein \(MVP - VB\)]

I have found Harlan to be an absolute wizard with worksheet formula
manipulations, so my gut feeling is that if Harlan suggested a formula to
you, it is more than likely to be one of the more efficient methods you will
be able to find for your task.

Rick


A good day everybody

I am looking for some changes in a formula , to get best speed for
calculate
it by excel application .
My first formula was :
=AND(COUNT(B1;B3;B5)<=1;COUNT(C1;C3;C3)<=1;COUNT(D1;D3;D5)<=1;COUNT(E1;E3;E­
5)<=1;COUNT(F1;F3;F5)<=1)
I was searching for an equivalent for this formula , and , thanks to
mr. Harlan Grove , I get the formula :
=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0,ISNUMBER(B1:F5)*{1;0;1;0;1})<=1)
in US formula sintax and in my locale sintax :
=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*{1|0|1|0|1})<=1)
and another :
=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0; ISNUMBER(B1:F5)*MOD(ROW(B1:F5);
2))<=1)
(this are array formulas ,must introduce with Ctrl-Shift-Enter)
For a better overview plase read and here :http://groups.google.ro/
group/microsoft.public.excel.worksheet.functions/browse_thread/thread/
25ce7b613b0a000b/5b152af7d8488120?hl=ro&lnk=gst&q=OLD
+Problem#5b152af7d8488120
_________________________________________________________________

for formula : =AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*{1|0|
1|0|1})<=1)
is possible to make some changes in it ,
maybe I'll can to transform it in a NON-array and NON-volatile
formula, or to change some function in it to work faster ?

Can this function to be an
equivalent :=AND(COUNT(MATCH(ROWS(B1:F5);TRANSPOSE({1;3;5}))>=1)) ??
It doesn't work still for me ...Please read and here , I'm not very
experimentate in functions and formulas :
http://www.decisionmodels.com/optspeedj.htm
Please very much for help

Thanks so much for your time and kinlyness

 

[ytayta555]

You can work and watch this formula working , copy from here the
next :
col :B C D E F


A #DIV/0! B #DIV/0!
5 6 TRUE 6 C
#DIV/0! 6 1 D E
6 F #DIV/0! G H
3 2 5



select cell B1 in excel page and paste ; the formula will return
FALSE;
if range C33 is deleted , formula will return TRUE

 

[ytayta555]

I have found Harlan to be an absolute .....

Indeed , It was amasing to get the equivalent for my formula , it
was
incredible ...
now I only searched and tried some last improvement for this very
complex
formula ...