Evaluations array formulas are incorrect (!/?)

Question

Level 1

0 points

Evaluations array formulas are incorrect (!/?)

Hi!

I'm working on a spreadsheet to return MULTIPLE corresponding values for ONE Lookup Value.

Consider the table array ("A2:C14"), in which you want to lookup the value "A" in column A which has multiple occurrences, and return all corresponding values in column C.

The complete formula is not the issue.

I know and have used it in Excel. It's woks in Excel.

The problem is that Numbers has returned inconsistent results in portion of the formula.

The entire formula would be =INDEX($C$2:$C$14, SMALL(IF($B$2=$A$2:$A$14, ROW($A$2:$A$14)-ROW($A$2)+1), ROW(1:1)))

Formula Part =IF($B$2=$A$2:$A$14, ROW($A$2:$A$14)) would evaluates to: {2, FALSE, FALSE, 5, FALSE, FALSE, 8, 9, FALSE, 11, FALSE, 13, FALSE}, wherein the numbers 2,5,8,9,11,13 indicate the worksheet row numbers containing "A".

However, The following value is returned:

{2,FALSE,FALSE,2,FALSE,FALSE,2,2,FALSE,2,FALSE,2,FALSE}!

Note the results of ROW($A$2:$A$14)): {2,2,2,2,2,2,2,2,2,2,2,2,2}. What's happened to the ROW Function?

The formula results should be {2,3,4,5,6,7,8,9,10,11,12,13}!

Is not the ROW Function in Numbers an array function?

I'm using Numbers 3.0 and OSX 10.9

MacBook Pro (13-inch Mid 2012), OS X Mavericks (10.9)

Posted on Oct 28, 2013 10:38 AM

Reply

Answer 1

Oct 28, 2013 11:40 AM in response to SergioMRamos

Numbers does not support array operations.

Here is a snippet from help regarding ROW():

The ROW function returns the row number of the row containing a specified cell.

ROW(cell)

cell: An optional cell reference to a single table cell.
The referenced cell that can contain any value, or be empty. Ifcell is omitted, as in =ROW(), the function returns the row number of the cell that contains the formula.

You may provide a feature suggestion to Apple using the menu item "Numbers > Provide Numbers Feedback"

Reply

Answer 2

SergioMRamos Author

Level 1

0 points

Oct 28, 2013 3:04 PM in response to Wayne Contello

Sorry, it's not help.

Numbers 3.0 supports array functions.

From http://help.apple.com/functions/mac/4.0/#ffa59b4edb

INDEX

The INDEX function returns the value in the cell located at the intersection of the specified row and column within a collection of cells or from an array returned by an array function.

"array function

Here are some examples of array functions:

=FREQUENCY($A$1:$F$5,$B$8:$E$8) returns an array of values, based on specified intervals.

=INDEX(FREQUENCY($A$1:$F$5,$B$8:$E$8),5) would return the fifth value in the array returned by the FREQUENCY function.

=TRANSPOSE($A$1:$E$3) returns an array containing the transposed values in the array $A$1:$E$3."

I questioned the result of the function ROW. Is there a bug or simple limitation?

Reply

Answer 3

SergioMRamos Author

Level 1

0 points

Oct 28, 2013 3:46 PM in response to SergioMRamos

This just keeps getting worse ... watch the results when I change the absolute reference for relative reference for addressing the set of cells ... Works! Then I did a test by moving the cell with the search string to another row ... That did not work properly. Only starts working when the formula is copied to the same start line of the array.

PS: Error messages are the same.

Message was edited by: SergioMRamos

Reply

Answer 4

fairab

Level 1

0 points

Mar 19, 2015 7:13 PM in response to SergioMRamos

I'M HAVING THE SAME PROBLEM!

It works perfectly in EXCEL....

I've sent feedback several times regarding this ability. I'm enjoying using NUMBERS yet it seems i'm being pushed back to using EXCEL. What I discovered is Numbers offers just 250 FUNCTIONS where as Excel offers over 400!!!!

PLEASE MAKE NUMBERS A REAL COMPETITOR TO EXCEL!

Reply

Answer 5

fairab

Level 1

0 points

Mar 19, 2015 7:15 PM in response to Wayne Contello

I'M HAVING THE SAME PROBLEM!

It works perfectly in EXCEL....

I've sent feedback several times regarding this ability. I'm enjoying using NUMBERS yet it seems i'm being pushed back to using EXCEL. What I discovered is Number

PLEASE MAKE NUMBERS A REAL COMPETITOR TO EXCEL!

Reply

Answer 6

SGIII

Level 8

35,264 points

Mar 19, 2015 8:01 PM in response to fairab

Guys, we're just fellow users here! If you want to give feedback to Apple then in your menu go to Numbers > Provide Numbers Feedback.

Numbers has a lot of functions, but not all the functions that Excel has. In particular, as Wayne points out, Numbers does not support most of Excel's array functions. Array functions are nice, but only a small subset of Excel users know how to use them. It seems Apple has decided they could be omitted to keep life simple in Numbers for most users.

In Numbers, there are usually fairly simple workarounds to do what you have been doing in Excel. If you describe what you are trying to do, then someone here should be able to give you pointers.

SG

Reply