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(D 1;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

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


"ytayta555" wrote in message
...
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(D 1;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

On 30 Iul, 20:28, "Rick Rothstein \(MVP - VB\)"
wrote:
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 ...

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 C3:D3 is deleted , formula will return TRUE