#### shrivallabha

##### Excel Ninja

Did you array enter JW's formula? It works for me here. So I'd not think it is regional settings issue.

- Thread starter jeffreyweir
- Start date

Did you array enter JW's formula? It works for me here. So I'd not think it is regional settings issue.

Yes, when I array-enter Jeff's formula over three rows, I get the expected results, but not when I try to use it in a single cell, as part of another formula.

However, Jeff in his most recent post indicated that he does NOT get #VALUE in Excel 2010 or 2013, whereas I am.

I have Excel 2010 on Windows 7, and Excel 2013 on Windows 8. I get the same results on both computers. (I have not changed any of my default settings... just the standard US settings.)

-Sajan.

Jeff;

All 3 formulas successfully returning values in a Array range. (Excel 2007, 2010)

when use F9 showing arrays correctly. (Excel 2007, 2010)

When used in COUNTA return 1 (Excel 2007, 2010)

When use F9 showing it's correct count. (Only in Excel 2010)

When use F9 #N/A error. (Excel 2007)

When used &"ABC" return correctly in Array range. No issues. (Excel 2007, 2010)

When use F9 return #VALUE! error. (Excel 2007, 2010)

In COUNTA showing correct count in the range , which are the count of #VALUE! errors. (Excel 2007, 2010)

Both versions are running in Windows 7 with just normal US settings.

Even in a single cell you will have to CSE irrespective of whether you trying for one result or doing a COUNTA.

For what it's worth, I have Excel 2010 - 32 bit - Win XP.

In short, steer clear of it.

By way of a demonstration, here's one weird thing that's been happening:

I put this in A1:F1 and named it d

1 2 3 A B C

In A2 I array entered =OFFSET(d,,{0,1,2,3,4,5},,)

When I evaluate this with F9, I get {1,2,3,"A","B","C"}, which is what I expect.

Then in A3 I array enter =OFFSET(OFFSET(d,,{0,1,2,3,4,5},,),,)

When I evaluate this with F9, I get {1,2,3,"A","B","C"}, which is what I expect.

THen in A4 I normally enter the same formula (i.e. I don't array enter it, but just push Enter).

When I evaluate this with F9, I get {1,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

When I go back and evaluate A2 or A3 I get what I did before, i.e. {1,2,3,"A","B","C"}

Now when I go and evaluate A4 again, I get {1,2,3,"A","B","C"}

This is weird. And this isn't the only weird behavior I've been getting when OFFSET(OFFSET is in the sheet somewhere.

Check out these links:

http://www.youtube.com/watch?v=T_EmoLfAJCM

http://www.youtube.com/watch?v=UbyiC-IZxDI

http://www.youtube.com/watch?v=7Rjh-LHvmCA

I've joined the discussion late, and haven't tried to follow why you're using OFFSET(OFFSET)...

but I replicate your weird spreadsheet behaviour, and apparently can fix it by tweaking =OFFSET(OFFSET(d,,{0,1,2,3,4,5},,),,) to

=OFFSET(OFFSET(d,,{0,1,2,3,4,5},,1),,)

With that sorted, maybe my OFFSET(OFFSET approach becomes a viable way to dereference an OFFSET array again.

I've uploaded a sample file here, if anyone is interested:

https://www.dropbox.com/s/n824v8h62xuoa5s/Using%20OFFSET%20to%20dereference%20OFFSET_20130821.xlsb

To be safe, it's best to use a 1 in both the Height AND Width argument.

=LOOKUP(MODE.MULT(IF(MATCH(m,m,0)=(ROW(m)-MIN(ROW(m))+{1,1}),ROW(m))),ROW(m),m)

That is a clever use of MODE.MULT to filter out stuff! With this challenge alone, I have developed a better appreciation for LOOKUP and MODE!

Following your idea, I am including the other two formulas using MODE.MULT for completeness:

Unique:

=LOOKUP(MODE.MULT(IF(COUNTIF(m,m)=1,ROW(m)*{1,1})),ROW(m),m)

Duplicate:

=LOOKUP(MODE.MULT(IF((COUNTIF(m,m)>1)*MATCH(m,m,0)=ROW(m)-MIN(ROW(m))+{1,1},ROW(m))),ROW(m),m)

-Sajan.

Following on from Sajan's previous comment about CONVERT, it turns out many other engineering functions and several date functions also accept OFFSET or INDIRECT with arrays in arguments. These extra functions were only available in the analysis toolpak in prior versions and they have the feature that references containing more than one cell need to be coerced to values.

For example: =ISODD(A1:A2) returns an error but ISODD(--A1:A2) and ISODD(OFFSET(A1,{0;1},)) are both ok.

The only function that can return an array of text or numbers from a range of one or more columns without needing to be entered into multiple cells, by the looks of it, is DGET.

For example: =DGET(OFFSET(A2:B6,{1;3;4},{0;1;0},-2,1),1,C2:C3) where C2:C3 are both blank.

Entering: =INDEX(A2:B6,{2;4;5},{1;2;1}) into a range would be easier and more efficient though!

=DGET(OFFSET(INDIRECT(TEXT(MODE.MULT(IF(FREQUENCY(COUNTIF(m,"<="&m)+ISTEXT(m)*COUNT(m),COUNTIF(m,"<="&m)+ISTEXT(m)*COUNT(m))>={1,1},MODE.MULT(100000*ROW(m)+COLUMN(m),100000*ROW(m)+COLUMN(m)))),"r0c00000"),0),,,-2),1,OFFSET(m,ROWS(m),,2,1))

Notes:

- - The range m is assumed to start in row 2 or below and contain blank rows following the last row.
- - Replace >= by = or > for uniques or duplicates, respectively.
- - Alternatively array-enter in a range: =CELL("Contents",INDIRECT(...,0)) instead of =DGET(OFFSET(INDIRECT(...,0)...)...) without the conditions on m above.

Thanks for this DGET formula.

I have never used any of the database functions in Excel, but had assumed that functions like DGET would be like a SQL Select statement, returning one or more items that match criteria. I was surprised and disappointed to read that it only returns one match. But thanks for the clever workaround you outlined, with OFFSET(...,-2).

Also gave me some additional ideas for determining row-wise MAX and MIN in a matrix.

By the way, not sure why you used INDIRECT(TEXT(...)) since DGET merely needs a reference. I got the same results with the following:

=DGET(OFFSET(m,MODE.MULT(IF(FREQUENCY(MATCH(m,m,0),MATCH(m,m,0))>={1,1},ROW(m)-MIN(ROW(m)))),0,-2),1,OFFSET(ZZ1,,,2,1))

Also, interesting technique with INDIRECT(TEXT...),0) as well to get R1C1-style references.

Regards,

Sajan.

http://excelxor.com/2014/10/09/coercing-array-returns-from-cse-resistant-formulas/

http://excelxor.com/2014/10/09/coercing-array-returns-from-cse-resistant-formulas/

The article appears to resolve some of the issues raised here. Among other things it demonstrates you can return valid arrays with INDEX using the following trick (although i still have little clue as to how this works):

=INDEX(A1:A5,N(IF(1,ROW(A1:A5))))

So for example Jeff's suggestion in post #6 above can be modified to this:

=INDEX(A:A,N(IF(1,SMALL(IF(FREQUENCY(MATCH(m,m,),MATCH(m,m,))>0,ROW(m)),ROW(OFFSET(A1,,,SUM(1/COUNTIF(m,m))))))))

And for getting a unique array from any rectangular range the awkward DGET formula above can be replaced with:

=CELL("Contents",INDIRECT(TEXT(N(IF(1,MODE.MULT(IF(FREQUENCY(COUNTIF(m,"<="&m)+ISTEXT(m)*COUNT(m),COUNTIF(m,"<="&m)+ISTEXT(m)*COUNT(m))>={1,1},MODE.MULT(100000*ROW(m)+COLUMN(m),100000*ROW(m)+COLUMN(m)))))),"r0c00000"),0))