extracting values from an array

Chris Davison · Apr 2nd, 2002, 11:17 AM

Evening all,

if I have an array as a result of a formula, say {1,2,3}

or even just a cell with {1,2,3} in it as an array

how do I get a different cell to return those 3 values : 1 2 3 ?

=OFFSET and =INDEX don't like me very much and were very unco-operative

merci beaucoup,
Chris

davers5 · Apr 2nd, 2002, 11:28 AM

How are you getting on array of values in one cell? If you can seperate them into several cells everything would be much easier.

Dave

Chris Davison · Apr 2nd, 2002, 11:39 AM

Hi Dave,

from a formula

however, I just saw the earlier posting which is very similar, which I'll merge into if I have any further problems

cheers
Chris

Jack in the UK · Apr 2nd, 2002, 11:44 AM

hi Chris.

correct me you wat a result to go in one cell thats sya 3 answers in one cell..

if so..

=sum(X) & " " & Sum(y) & " " & Sum(z)

this is not a real formula but will this not do the job...

& links results//

say A1 = Chris
say B1 = D

then = A1 & " " & B1 (say in C1)

This will result/return in in C1

Chris D

does this help or anyone know better .....

Aladin Akyurek · Apr 2nd, 2002, 12:11 PM

Quote:

On 2002-04-02 10:39, Chris Davison wrote:
Hi Dave,

from a formula

however, I just saw the earlier posting which is very similar, which I'll merge into if I have any further problems

cheers
Chris

Yep, very much so. Mark offered using a named array.

What kind of formula does produce your constant array?

Aladin

Chris Davison · Apr 2nd, 2002, 01:49 PM

a formula that returns 5 random numbers from a population of 50....

I have an answer, but it's an array.

I can't get the damn thign to *show* !

Mark W. · Apr 2nd, 2002, 03:48 PM

Quote:

On 2002-04-02 12:49, Chris Davison wrote:
a formula that returns 5 random numbers from a population of 50....

I have an answer, but it's an array.

I can't get the damn thign to *show* !

If the elements of the resultant array are separated by commas (,) then prior to entering your formula select 5 adjacent cells on a single row, type your formula, and press the Control+Shift+Enter key combination.

If the elements of the resultant array are separated by semicolons (

then prior to entering your formula select 5 adjacent cells on a single column, type your formula, and press the Control+Shift+Enter key combination.

For more info on array formulas see the Excel Help topic for "About array formulas and how to enter them". Pay special attention to the paragraph that begins with "To calculate multiple results with an array formula..."

[ This Message was edited by: Mark W. on 2002-04-02 14:50 ]

Apr 2nd, 2002, 11:44 AM	#4
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	hi Chris. correct me you wat a result to go in one cell thats sya 3 answers in one cell.. if so.. =sum(X) & " " & Sum(y) & " " & Sum(z) this is not a real formula but will this not do the job... & links results// say A1 = Chris say B1 = D then = A1 & " " & B1 (say in C1) This will result/return in in C1 Chris D does this help or anyone know better ..... __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 2nd, 2002, 11:17 AM	#1
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Evening all, if I have an array as a result of a formula, say {1,2,3} or even just a cell with {1,2,3} in it as an array how do I get a different cell to return those 3 values : 1 2 3 ? =OFFSET and =INDEX don't like me very much and were very unco-operative merci beaucoup, Chris

Apr 2nd, 2002, 11:28 AM	#2
davers5 Board Regular Join Date: Feb 2002 Posts: 255	How are you getting on array of values in one cell? If you can seperate them into several cells everything would be much easier. Dave

Apr 2nd, 2002, 11:39 AM	#3
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Hi Dave, from a formula however, I just saw the earlier posting which is very similar, which I'll merge into if I have any further problems cheers Chris

Apr 2nd, 2002, 01:49 PM	#6
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	a formula that returns 5 random numbers from a population of 50.... I have an answer, but it's an array. I can't get the damn thign to show !