Use values from a single cell as an Array Constant?

[jodywally]

Experts,

Is there any way to make an array formula use an array constant by referencing all the value for that array constant from different, single cell?
For example, if I have a simple array formula like
{=SUM({1,2,3,4}*{1,2,3,4})} (which =30), can I store the array constant in cell A1 so that the formula would could read {=SUM(A1*{1,2,3,4})}? Right now, I store the values in A1 as ={1,2,3,4}, which Excel recognizes as an array, but the formula treats it as {1},not {1,2,3,4}, and therefore the result = 10, not 30. I've triend various formats, but unless I type the array constant directly into the formula, the values are never recognized as an array.

BTW, I know that I could define a name for the array constant, but that won't work for my purposes since I actually want to store those values in a single field of a database, return them to the worksheet via query, and then use the array formula to reference them from the resulting table.

Any ideas?

 

[trunten]

Put your array constant in A1 like so: {1,2,3,4}
Then go to formulas -> defined names and create a new named range called wahtever you like (lets say array) and enter this as the formula: =Evaluate($A$1)
Then you can use the constant like so:
{=SUM(Array*Array)}
(which equals 30)

edit, and now you know that just think of all the cool things you could do....

 

[trunten]

Put your array constant in A1 like so: {1,2,3,4}
Then go to formulas -> defined names and create a new named range called wahtever you like (lets say array) and enter this as the formula: =Evaluate($A$1)
Then you can use the constant like so:
{=SUM(Array*Array)}
(which equals 30)

edit, and now you know that just think of all the cool things you could do....

Or of course just create a named constant...too clever for my own good there I think.

 

[jodywally]

Trunten,

Thanks for the reply, but as I mentioned in my original post, I can't use a 'defined name' constant since the values I want to use will be stored as a string in a database which I'll retrieve as needed. I want the formula to reference the cell and interpret the contents as an array constant, without knowing in advance what it is.

I'm not even sure it's possible, but it seems like it should be, ya know?

Jody

 

[trunten]

I'm not sure that's possible then tbh. But you could still use my first suggestion, as the named formula will pick up whatever value is in cell A1 as long as it's formatted as an array constant. It's a work-around yes, but it may be useful.

 

[T. Valko]

Experts,

Is there any way to make an array formula use an array constant by referencing all the value for that array constant from different, single cell?
For example, if I have a simple array formula like
{=SUM({1,2,3,4}*{1,2,3,4})} (which =30), can I store the array constant in cell A1 so that the formula would could read {=SUM(A1*{1,2,3,4})}? Right now, I store the values in A1 as ={1,2,3,4}, which Excel recognizes as an array, but the formula treats it as {1},not {1,2,3,4}, and therefore the result = 10, not 30. I've triend various formats, but unless I type the array constant directly into the formula, the values are never recognized as an array.

BTW, I know that I could define a name for the array constant, but that won't work for my purposes since I actually want to store those values in a single field of a database, return them to the worksheet via query, and then use the array formula to reference them from the resulting table.

Any ideas?

Array constants can't be cell references.

 

[jodywally]

Thanks, that's what I was afraid of, but now I can focus on plan B.

Jody

 

[T. Valko]

Thanks, that's what I was afraid of, but now I can focus on plan B.

Jody

Can you put the array elements in a range (array) of cells?

Book1
	A	B
2	1	1
3	2	2
4	3	3
5	4	4
6	_	_
7	30	_
Sheet1

=SUMPRODUCT(A2:A5,B2:B5)

 

[jodywally]

Yes, that's effectively my plan B.

What I'm doing is tracking numerous progressive tiered pricing plans for different customers. I originally wanted to store the tier details (Min Max,price, etc.) as strings in a a field of the application record in my DB (salesforce), and then use arrays to 'build' the final price in Excel (after populating an Excel table via query).

However, with no easy way to interpret the strings as an array constant, I've instead chosen to add a related table to my Application records and store each tier as its own record. Now I don't even need arrays, as I simply evaluate the total usage against each tier record, thus generating a discrete dollar value in the cell next to the table. At the customer level, I simply SUMIFS the amounts for that customer and I've got my total fee.

Not as tricky as using arrays, but certainly faster, and easier for others to manipulate.

Thanks.
Jody

 

[pgc01]

Trunten,

Thanks for the reply, but as I mentioned in my original post, I can't use a 'defined name' constant since the values I want to use will be stored as a string in a database which I'll retrieve as needed. I want the formula to reference the cell and interpret the contents as an array constant, without knowing in advance what it is.

Hi Jody

I'm probably not understanding something. I understand you don't want to define a named array constant, but I don't understand why the named formula defined by trunten in post #2 would not work. The value of the named formula will always reflect the current value in $A$1, so whenever you retrieve a new value to $A$1 the formulas that use the named formula will update automatically.

Another option if you have to refer to many cells would be to use a small udf that evaluates a cell.