PDA

View Full Version : Solved: Array Constants



pete_bristol
02-15-2006, 02:18 AM
Hi,

I've been getting to grips recently with array formulas and curly brackets.

I understand, for instance, that if i wanted to sum the top three entries in a range A1:A10 i would enter the formula @sum(large(A1:A10,{1,2,3})).

However, what if i wanted to specify the {1,2,3} as "variables".

I think i have tried just about every conceivable way of replacing the {1,2,3} with cell references, INDIRECT() etc, but with no joy.

Perhaps it is not possible?

I would really appreciate your help.


Regards,


Pete

XLGibbs
02-15-2006, 07:56 AM
How do you mean "variables"

the array can hold cell references, however, instead of bracketing them as just the array of top 3 you would have it contain the array of cell references adn confirm the formula with CTRL-SHIFT-ENTER so it looks like..

{=SUM(LARGE(B1:B10,D1:F1))}

after you confirm with CTRL SHIFT ENTER (you can't simply type the brackets)

PS Welcome to the board!

Bob Phillips
02-15-2006, 08:09 AM
I understand, for instance, that if i wanted to sum the top three entries in a range A1:A10 i would enter the formula @sum(large(A1:A10,{1,2,3})).

However, what if i wanted to specify the {1,2,3} as "variables".

I think i have tried just about every conceivable way of replacing the {1,2,3} with cell references, INDIRECT() etc, but with no joy.

The standard way is to use the ROW function, like so

=SUM(LARGE(A1:A10,ROW(1:3)))

You can even store a final part in a cell, so say store 3 in B1 and use indirect in conjunction

=SUM(LARGE(A1:A10,ROW(INDIRECT("1:"&B1))))

pete_bristol
02-16-2006, 12:59 AM
Thank you very much, that works a treat!

Obvious when you know how!

Pete

geekgirlau
02-16-2006, 05:24 PM
Hi Pete, don't forget to mark this thread as "Solved" (go to the "Thread Tools" button at the top of the page).