Using an array, written out explicitly in curly braces in a cell, within another formula

**Heff88** · 11-06-2019, 08:07 AM

I have produced an array in one cell (using a TEXTJOIN) that I would like to use in further calculations. The string in that cell can be formatted any way I want - I have formatted it as {1,2,3} - because this is how Excel normally formats arrays.

However, when I try to pass this to another Excel function it breaks - e.g. if the string {1,2,3} is in cell A1, then: AVERAGE(A1) = #DIV/0!

If I do this manually and simply write: AVERAGE({1,2,3}) = 2 as it should.

In other words, Excel is parsing the string {1,2,3} differently when it's hand-written into a formula, and when that string {1,2,3} is explicitly written out in another cell.

I've also tried embedding the TEXTJOIN within the AVERAGE but that doesn't work either (and I'd rather break the two steps up anyway).

Any thoughts?

**Special-K** · 11-06-2019, 08:12 AM

Why dont you just pass the individual values to the AVERAGE function, e.g.
if you cells you are TEXTJOINing are A1 B1 C1 to produce 1,2,3
why not just do

AVERAGE(A1, B1, C1)

**KOKOSEK** · 11-06-2019, 08:30 AM

A1={1,2,3}
B1=TYPE(A1) => 1 (text)

**NARAYANK991** · 11-06-2019, 08:33 AM

Hi ,

If you use the Macro 4 Evaluate function , you can get what you want , or so I think.

Define a named range StringArray , and in the Refers To box enter :

Formula:

Please Login or Register  to view this content.

where cell G1 contains {1,2,3} entered as a text string.

Now , in any worksheet cell , enter the formula :

Formula:

Please Login or Register  to view this content.

and the cell should display 2.

Narayan

**Heff88** · 11-06-2019, 08:37 AM

Removed - cannot see how to delete post.

**Heff88** · 11-06-2019, 08:40 AM

Originally Posted by Special-K

Why dont you just pass the individual values to the AVERAGE function, e.g.
if you cells you are TEXTJOINing are A1 B1 C1 to produce 1,2,3
why not just do

AVERAGE(A1, B1, C1)

The MWE was just a conceptualisation of my real issue - and solving it would help me solve my actual problem, which is more complex.

**Heff88** · 11-06-2019, 08:41 AM

Originally Posted by KOKOSEK

A1={1,2,3}
B1=TYPE(A1) => 1 (text)

Is this another way of saying: I can't do what I want?

**rorya** · 11-06-2019, 08:44 AM

You can, but you need either VBA or XLM, as mentioned.

**Heff88** · 11-06-2019, 08:44 AM

Originally Posted by NARAYANK991

Hi ,

If you use the Macro 4 Evaluate function , you can get what you want , or so I think.

Define a named range StringArray , and in the Refers To box enter :

Formula:

Please Login or Register  to view this content.

where cell G1 contains {1,2,3} entered as a text string.

Now , in any worksheet cell , enter the formula :

Formula:

Please Login or Register  to view this content.

and the cell should display 2.

Narayan

Thanks for the answer: is this dependent on using VBA (not sure what "macro 4 array" is)? I would rather avoid this if possible, though aware it may be impossible. It just seems like I'm soooo close and it would render my final spreadsheet much simpler.

**NARAYANK991** · 11-06-2019, 08:56 AM

Originally Posted by Heff88

Thanks for the answer: is this dependent on using VBA (not sure what "macro 4 array" is)? I would rather avoid this if possible, though aware it may be impossible. It just seems like I'm soooo close and it would render my final spreadsheet much simpler.

Hi ,

No , no VBA is involved , though because it is a Macro 4 function , you will have to save the file as a .xlsm or .xlsb file.

Narayan

Using an array, written out explicitly in curly braces in a cell, within another formula

LinkBack

Thread Tools

Rate This Thread

Display

Using an array, written out explicitly in curly braces in a cell, within another formula

Re: Using an array, written out explicitly in curly braces in a cell, within another formu

Re: Using an array, written out explicitly in curly braces in a cell, within another formu

Re: Using an array, written out explicitly in curly braces in a cell, within another formu

Re: Using an array, written out explicitly in curly braces in a cell, within another formu

Re: Using an array, written out explicitly in curly braces in a cell, within another formu

Re: Using an array, written out explicitly in curly braces in a cell, within another formu

Re: Using an array, written out explicitly in curly braces in a cell, within another formu

Re: Using an array, written out explicitly in curly braces in a cell, within another formu

Re: Using an array, written out explicitly in curly braces in a cell, within another formu

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Sumif with Curly Braces { }

[SOLVED] How to enter array formula with curly brackets into cell

How to add array formula that contains curly braces using VBA?

curly braces

Array formula that uses last written cell

[SOLVED] Can I update Multiple Forumlae With Curly Braces All At Once?

What do curly brackets /braces {} do in VBA

Bookmarks

Bookmarks

Posting Permissions