non constant arrays?

what are you trying to do? cant you just
=SUM(A1+A2,A2-A1,A3+A4) or are you trying to create an array say {3,1,7} for use in another function?

Trying to create the array {A1+A2, A2-A1, A3+A4}

yes but for what use?

hmmmmmm that has me stumped.
possibly via vba
ok anyone else got an offer?

Please Login or Register  to view this content.

=Agg(A1+A2, A2-A1, A3+A4)

Note: If you wish to see the individual values of Shg's function, then it can be entered as an array, select the number of cells = to the number of arrays you are calculating. Enter with CNTRL SHFT ENTER

Hope that helps.

I think shg's post is more to illustrate how all three results are returned in a single 1D Array (you see only the first value in the cell however all three exist - eg SUM(Agg(...)) would return the aggregate of the Array).

The same can be achieved using formulae but it is significantly less flexible:

=INDEX(CHOOSE({1,2,3},A1+A2,A2-A1,A3+A4),0)

(same point holds - ie you see one value but all exist within [highlight and press F9 or again see: SUM(INDEX(...)) which would aggregate the 1d array])

The native approach is less flexible given the {1,n} would need to be adjusted based on the number of arguments supplied in the CHOOSE whereas shg's UDF utilises a ParamArray and thus will adjust dynamically

Donkey

unfortunately your method does not actually return an array, it returns a number within an array (sort of like a read command like array[5]). I'm looking for something actually returns an array, ie. similar to row(), column(), etc.

Unforunately the mac2008 version of excel i'm using does not support VBA =(

What I'm ultimately trying to do is:

I have a large n x m matrix of percentages, from E8 to I12

I need to create an array {(1-E8)*(1-F8)*...*(1-CURRENTCOLUMN8) , (1-E9)*(1-F9)*...*(1-CURRENTCOLUMN9) , ... , (1-E12)*(1-F12)*...*(1-CURRENTCOLUMN12)}

and multiply this huge array by another array

Originally Posted by themachine

Donkey

unfortunately your method does not actually return an array, it returns a number within an array

Please read my post again, specifically:

Originally Posted by D.O

ie you see one value but all exist within [highlight and press F9 or again see: SUM(INDEX(...)) which would aggregate the 1d array]

If you're ever unsure as to what is being returned be sure to make use of the F9 feature in the Formula Bar - ie highlight the INDEX function and press F9 - you will see the array of results.

A cell will only ever display a single result - whereas in fact the INDEX holds all values by virtue of the 0 row_index - do not confuse cell content with formula "output".

Unforunately the mac2008 version of excel i'm using ...

That would be a good thing to have in your profile, rather than Excel 2007.

Originally Posted by DonkeyOte

Please read my post again, specifically:



If you're ever unsure as to what is being returned be sure to make use of the F9 feature in the Formula Bar - ie highlight the INDEX function and press F9 - you will see the array of results.

A cell will only ever display a single result - whereas in fact the INDEX holds all values by virtue of the 0 row_index - do not confuse cell content with formula "output".

Problem is when I try and do something like C1:C3*INDEX(CHOOSE({1,2,3},A1+A2,A2-A3,A3+A2),0) the INDEX() part is the same value, ie. it doesn't change to A2-A3, A3+A2, etc.

If you select D1:D3 and array-enter the formula, you'll see all three values returned.

I'm pretty sure array multiply doesn't do it:

http://i.imgur.com/tCGgt.png

A5:A7 were array formula entered, but they should be:

177
440
305

Where as they are:

177=(A1+A2)*3
236=(A1+A2)*4
295=(A1+A2)*5

SUM() does seem to iterate over the index and work as intended, but Array multiply certainly doesn't

That formula generates 9 results. Maybe what you're looking for is

=C1:C3*CHOOSE({1;2;3}, A1+A2, A1+A1, A3+A1)

Note the semicolons in lieu of the commas to make the second array a column array.

Ahh, I see what you mean. It was creating a 3x3 matrix

{1;2;3} is a column array where as {1,2,3} is a row array?

The latter is a row array; neither is more 'normal' than the other.

No need for any of that; =E8:E99*F8:F99, array-entered.

Originally Posted by shg

No need for any of that; =E8:E99*F8:F99, array-entered.

Sorry I wasn't specific enough, unfortunately I need to be able to drag/expand at will

Column 1:

{PRODUCT($E8:F8),PRODUCT($E9:F9),...,PRODUCT($E99:F99)}

allows me to drag it to the next column to create

Column 2:

{PRODUCT($E8:G8),PRODUCT($E9:G9),...,PRODUCT($E99:G99)}

I'm really trying to create this

http://i.imgur.com/5HXgw.jpg

Essentially a weighted average of risk reductions scalable across multiple components and reducable by percentages. My implementation is kinda disgusting, as I have 2 other sheets used mainly to just store data for taking (1-percentages), and another for multiplying the reduction up to the respective dates

Please post workbook, not pictures.

Yoy can drag the formula =$E8:$E99*F8:F99, to the right to get those results, but why bother with an array formula at all for that?

The problem is that you're still multiplying by only two arrays independent of position, where as the product one multiplies a variable number of arrays dependent on the position

I have uploaded the workbook,

What I'd really like is to remove the products/complement sheets so the main sheet isn't dependent on the other two. It's a very ugly implementation

Rather than using two sheets of helper cells, you could just use a few columns. Changes highlighted, see also defined names.

DaddyLongLegs might be able to suggest a way to get rid of the helper columns.

I can't see any simple way to do that (or really even a complicated way) - I think in this situation you already have the best approach, shg. Even if you could get rid of the helper cells it would be considerably more complex to do so.....

Many thanks for the help.