Array Functions

Alan Beban has a downloadable Excel file chock full of array functions. He’s written these functions in VBA to allow you to maniupulate arrays.

From Alan’s site:

The file “Array Functions” contains 27 Function Procedures and 2 Sub Procedures for manipulating arrays (and worksheet ranges).

The best part is that the code is unprotected so you can see (and learn) what’s going on. Alan probably knows more about working with arrays than anyone. He’d have to just to write these functions. Check them out if you work with arrays.

Posted in Uncategorized

9 thoughts on “Array Functions

  1. Do you have Alan Beban’s email address? I attempted to download his array functions workbook after reading about it on your site. It seems to have overwritten all the macros in my Personal Workbook!!

  2. hi,

    it seems Alan Beban has removed his website. so the downloadable book is no longer available. does anyone have it and who can send it to my email addy: sifar786 at gmail dot com.

  3. Hello, can someone please help me figure out why the following arrayformula works

    ={OFFSET(INDIRECT(“‘GEN rates’!E”&MATCH(RIGHT(tool!K23,2)&A2&tool!F21,’GEN rates’!A68:A79&’GEN rates’!B68:B79&’GEN rates’!C68:C79,0)),ROW($A$68)-1,0)*(tool!M23-tool!H23)+OFFSET(INDIRECT(“‘GEN rates’!E”&MATCH(RIGHT(tool!K23,2)&A2&tool!H21,’GEN rates’!A68:A79&’GEN rates’!B68:B79&’GEN rates’!C68:C79,0)),ROW($A$68)-1,0)*(tool!M23-tool!F23)+IF(RIGHT(tool!K23,2)=”EU”,(tool!G23+tool!I23)*’GEN rates’!E80,0)}

    while the following one with nested IF returns VALUE# error?

    ={IF(‘GT support’!B11<3,OFFSET(INDIRECT("'GEN rates'!E"&MATCH(RIGHT(tool!K23,2)&A2&tool!F21,'GEN rates'!A68:A79&'GEN rates'!B68:B79&'GEN rates'!C68:C79,0)),ROW($A$68)-1,0)*(tool!M23-tool!H23)+OFFSET(INDIRECT("'GEN rates'!E"&MATCH(RIGHT(tool!K23,2)&A2&tool!H21,'GEN rates'!A68:A79&'GEN rates'!B68:B79&'GEN rates'!C68:C79,0)),ROW($A$68)-1,0)*(tool!M23-tool!F23)+IF(RIGHT(tool!K23,2)="EU",(tool!G23+tool!I23)*'GEN rates'!E80,0),"incl.in FOB")}

    I have been racking my brains to no avail so far, and seek any kind of advice to make the array formula work under the additional IF condition in the second case (otherwise the formulas are the same)

    Many thanks in advance,

  4. @jiri. The problem is that parts of your formula return arrays and other parts return scalar values. I put in some data and I Ctrl+= on parts of your formula. I get this

    In a formula that doesn’t use functions (kind of), you can get away with mixing arrays and scalars – Excel just takes the first element of the array. By “doesn’t use functions” I mean that there are no functions surrounding the array. There are functions that return an array or are part of an expression that return an array, but that array is not an argument to another function.

    Once you make an array an argument to a function, all the array’s have to have the same number of elements. Because you have a mix of array and scalar, Excel sees that you don’t have arrays with the same number of elements and returns the Value error. It doesn’t matter that IF isn’t an aggregate function and that computation of the argument has nothing to do with IF – it’s just the way it is.

    The answer for you is to get rid of the arrays. From what I can tell, you don’t need them anyway.

    Unlike INDIRECT, INDEX can take an array and return a scalar. This makes all your arguments scalars and you can wrap an IF around them all you want.

  5. Hi, Dick
    thanks you a lot for your valuable insights!
    Lately, I have been involved in an issue of making a criteria range dynamic in a SUMIF function nested in SUMPRODUCT function

    ={SUMPRODUCT(SUMIF(prices!$D$2:$D$12;N756:P756;prices!$E$2:$E$12))}

    This function works and returns the desired result, but I would need to make the end of the criteria range dynamic – adjusted to the last value in a row. So, fex.on the line number 756, the last criteria value is in column P (therefore P756), but on the next line 757 it can be in column O, column R etc.
    I might use an auxiliary column B which says in numbers, how many values are entered to the right of N column. E.g. for line 756, the value in the cell B756 is 3 to signify that the SUMIF criteria are 3, in the cells N756, O756 and P756.

    Would you have any ideas how make it work?

    Many thanks,
    Jiri

  6. Jiri: You can make that second argument as wide as you want. If there are blanks, it won’t affect the result of the sumif. So make every line N:X (or whatever the widest you will have is).


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.