Interpret a squence of cells as an array

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

VDU - 23 Jan 2008 16:35 GMT

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,

Reply to this Message

Gaurav - 23 Jan 2008 16:46 GMT

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.

> Hello,
>
[quoted text clipped - 18 lines]
> Thank you.
> Regards,

Reply to this Message

VDU - 23 Jan 2008 21:10 GMT

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,

> 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
[quoted text clipped - 24 lines]
> > Thank you.
> > Regards,

Reply to this Message

Gaurav - 23 Jan 2008 21:43 GMT

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

> 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
[quoted text clipped - 45 lines]
>> > Thank you.
>> > Regards,

Reply to this Message

VDU - 23 Jan 2008 22:27 GMT

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,

> 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
[quoted text clipped - 51 lines]
> >> > Thank you.
> >> > Regards,

Reply to this Message

Gaurav - 23 Jan 2008 22:34 GMT

Glad it worked.

Cheers!
Gaurav

> It works but, a little different,
>
[quoted text clipped - 82 lines]
>> >> > Thank you.
>> >> > Regards,

Reply to this Message