Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF function won't post cell values | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
numerical integration | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
How to use a cell value as Table Array in VLOOKUP worksheet function | Excel Discussion (Misc queries) |