Function CELL in array formulas
 

Hi all,

yesterday I was trying a solution to a post. It was involving a column
containing dates and percentages. The OP wanted the max of the
non-dates.

I thought I would attack this problem using CELL:
=MAX(IF(LEFT(CELL("format",A1:A5),1)<"D",A1:A5))

It did not work, at least in the array formula. It worked perfectly
when I used an extra column, but this is beside the point. Is it
anywhere stated that CELL cannot be used in array formulas? Am I
missing something?

Regards,
Kostis Vezerides

Function CELL in array formulas
 

Evaluating the formula it seems that CELL just will not return an array of
values. Some functions won't, WEEKNUM springs to mind, so cannot be used
within an array formula/function.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vezerid" wrote in message
ups.com...
Hi all,

yesterday I was trying a solution to a post. It was involving a column
containing dates and percentages. The OP wanted the max of the
non-dates.

I thought I would attack this problem using CELL:
=MAX(IF(LEFT(CELL("format",A1:A5),1)<"D",A1:A5))

It did not work, at least in the array formula. It worked perfectly
when I used an extra column, but this is beside the point. Is it
anywhere stated that CELL cannot be used in array formulas? Am I
missing something?

Regards,
Kostis Vezerides

Function CELL in array formulas
 

Hi Bob,
(did you ever come to Greece for vacation?)

I had tried the evaluation myself and something had seemed wrong. Now I
understand that my eye was expecting an array and I was getting a
scalar. From what I understand this is a group of functions which, for
reasons known only to implementors, do not go well with array formulas.
I can understand it with range functions like OFFSET, INDIRECT etc but,
for the life of me, I cannot understand why this would be necessary for
WEEKNUM. BTW, isn't WEEKNUM an ATP function?

Regards,
Kostis


Bob Phillips wrote:
Evaluating the formula it seems that CELL just will not return an array of
values. Some functions won't, WEEKNUM springs to mind, so cannot be used
within an array formula/function.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vezerid" wrote in message
ups.com...
Hi all,

yesterday I was trying a solution to a post. It was involving a column
containing dates and percentages. The OP wanted the max of the
non-dates.

I thought I would attack this problem using CELL:
=MAX(IF(LEFT(CELL("format",A1:A5),1)<"D",A1:A5))

It did not work, at least in the array formula. It worked perfectly
when I used an extra column, but this is beside the point. Is it
anywhere stated that CELL cannot be used in array formulas? Am I
missing something?

Regards,
Kostis Vezerides

Function CELL in array formulas
 

Hi Kostis,

Not this time around, unfortunately. Lynne and I could not work a week that
we both could do, so it will have to be next year. I still have your notes
though for that time.

It seems odd that any of the functions should not be able to return an
array, there may be a reason, but it is beyond me. With some, such as
WEEKNUM, a function alternative is possible, but can't see how to do a date
test without CELL.

Yes, WEEKNUM is ATP, until 2007.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vezerid" wrote in message
oups.com...
Hi Bob,
(did you ever come to Greece for vacation?)

I had tried the evaluation myself and something had seemed wrong. Now I
understand that my eye was expecting an array and I was getting a
scalar. From what I understand this is a group of functions which, for
reasons known only to implementors, do not go well with array formulas.
I can understand it with range functions like OFFSET, INDIRECT etc but,
for the life of me, I cannot understand why this would be necessary for
WEEKNUM. BTW, isn't WEEKNUM an ATP function?

Regards,
Kostis


Bob Phillips wrote:
Evaluating the formula it seems that CELL just will not return an array
of
values. Some functions won't, WEEKNUM springs to mind, so cannot be used
within an array formula/function.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vezerid" wrote in message
ups.com...
Hi all,

yesterday I was trying a solution to a post. It was involving a column
containing dates and percentages. The OP wanted the max of the
non-dates.

I thought I would attack this problem using CELL:
=MAX(IF(LEFT(CELL("format",A1:A5),1)<"D",A1:A5))

It did not work, at least in the array formula. It worked perfectly
when I used an extra column, but this is beside the point. Is it
anywhere stated that CELL cannot be used in array formulas? Am I
missing something?

Regards,
Kostis Vezerides