Array not liking formulas within {}

[Raesu]

Hello,


I'm using an array formula to populate an axis of a chart based on time period:


'

{=CHOOSE($A$39,{"Jan","Feb","Mar","Q1"},{"Apr","May","Jun","Q2"},{"Jul","Aug","Sep","Q3"},{"Oct","Nov","Dec","Q4"},{"Q1","Q2","Q3","Q4","YearTotal"})}

'


I'm trying to put a number along with the month but Excel is not accepting it. For example, taking the "Jan" and turning it into "Jan ("&F50&")", is rejected. Any logical ideas why this is happening? Much appreciated, thanks.


Raesu

 

[Jordan]

I could be wrong about this (one of the gurus may need to step in and confirm), but I don't think you can put cell references in arrays. What you can do however is reference a cell array directly. So, for example, in cells A1:D1 you could put "Jan","Feb","Mar","Q1" respectively in each cell. Then, in your formula, you would do something like:


{=CHOOSE($A$39,A1:D1,...)}


With this setup, you could change the value in cell A1 from "Jan" to ="Jan (" & F50 & ")" and achieve the same functionality as you had before.

 

[SirJB7]

Hi, Raesu!

As no guru happened to step into his topic, I'd dare to agree with Jordan. Until someone prove us wrong.

Regards!

 

[Jordan]

That's a dangerous gamble, my friend....

 

[SirJB7]

@Jordan

Hi, mate!

If it weren't it'd be less (if not nothing) entertaining...

Regards!

 

[NARAYANK991]

Hi Jordan ,


This is what Walkenbach has to say ( http://office.microsoft.com/en-in/excel-help/more-arrays-introducing-array-constants-in-excel-HA001087291.aspx ) :

[pre]

Elements you can use in constants

Array constants can contain numbers, text, logical values such as TRUE and FALSE, and error values
such as #N/A. You can use numbers in the integer, decimal, and scientific formats. If you include
text, you must surround that text with double quotation marks.

Array constants can't contain additional arrays, formulas, or functions. In other words, they can
only contain text or numbers separated by commas or semicolons. Excel gives you an error message
when you use something like {1,2,A1:D4} or {1,2,SUM(Q2:Z8)}.

Also, numeric values can't contain percent signs, dollar signs, commas, or parentheses.

[/pre]
Narayan

 

[Raesu]

Solved, thank you!