Interpret a squence of cells as an array

[VDU]

Hello,

I think this might be frequent question but I didn't find it in my search.
If you can point me to another relevant topic, it's just as fine.

My question is, how can I interpret a sequence of cells as an array for
functions that require an array.

For example, when I make a simple sum, I can enter an array or a sequence
just as easy by using the comma separator and selecting specific cells. On
the other hand, if the function requires other parameters, if I use the comma
separator to insert a sequence, the second cell will be interpreted as the
2nd parameter, not as part of the first parameter.

is there a function where I can make a series of cells (ex: C1, C4, C7, C10,
...., C(3x+1) ) as an array in a function that requires an array (ex:
small(array, k)).

Thank you.
Regards,

 

[Gaurav]

I am not sure but I think you can use a named range. Select all the cells
you want to add to the array, click on the left top box where you see the
cell address, write a name there ex. 'myrange' and press enter. After this
whenever you write 'myrange' in any formula, it will refer to the cells you
selected.

 

[VDU]

It works, thank you, but unfortunately, now I have another problem, this
formula was meant to be copied over more than 100 rows. When you copy a
formula from another row, excel changes the formula so that it refers to
cells in the same columns but on the new row (that if you don't use $). It
doesn't do the same for this range, it remains the same on all rows.

Defining over 100 ranges is very laborious, is there a shortcut that could
help me do this faster or is there another method altogether?

Again thank you for your answer, it works in the case I described but not in
my real case.

Regards,

 

[Gaurav]

Once you have named the range, go to Insert>Name>Define. You will see all
the cells you selected in the box below...just remove the $ from there and
press OK. Now drag the formula and it will adjust. If this is what you want.

Thanks

 

[VDU]

It works but, a little different,

For those who have the 2007 version this is actually located in the formulas
tab > defined names group > name manager. In that dialogue the name must be
selected and in the "Refers to" box the $ have to be deleted.

Thank you Gaurav, most helpful. I still think a function like Array(cell1,
cell2, cell3....) that will make the group of cells be interpreted as an
array (or range) would have been much more helpful and easy to use (and
easyer to guess) but this function does not exist yet, maybe in the next
excel version.

Regards,

 

[Gaurav]

Glad it worked.

Cheers!
Gaurav

It works but, a little different,

For those who have the 2007 version this is actually located in the
formulas
tab > defined names group > name manager. In that dialogue the name must
be
selected and in the "Refers to" box the $ have to be deleted.

Thank you Gaurav, most helpful. I still think a function like Array(cell1,
cell2, cell3....) that will make the group of cells be interpreted as an
array (or range) would have been much more helpful and easy to use (and
easyer to guess) but this function does not exist yet, maybe in the next
excel version.

Regards,