zondag 24 november 2013

Excel: Some Reflections on Matrices in Names based on Text, Used in Graphs

Data:

A1:D12 filled with jan-dec:

jan
jan
jan
jan
feb
feb
feb
feb
mar
mar
mar
mar
apr
apr
apr
apr
may
may
may
may
jun
jun
jun
jun
jul
jul
jul
jul
aug
aug
aug
aug
sep
sep
sep
sep
oct
oct
oct
oct
nov
nov
nov
nov
dec
dec
dec
dec

Example A

AllText =A1:D12 &""

Adding &"" changes the whole name into one continuous array, reading from left to right and from top to bottom. We can use this in a graph (see Example A). We can not read the elements using the INDEX function like this:

=INDEX(AllText;13)

The INDEX does not see this as a continuous array.

We still could use:

=INDEX(AllText;4;3)

But there is nothing special about that.

Example B

ColumnText1 =A1:A12
ColumnText 2 =D1:D12

ColumnText Total = ColumnText 1: ColumnText 2

This name refers to the individual names including the columns between the individual names.

ColumnText Total = ColumnText 1: ColumnText 2 &""

Adding &""  changes the range into one continuous range, identical to AllText.

Example C

ColumnText Combi = ColumnText 1; ColumnText 2

This is automatically a continuous range without adding &"". We can use this in a graph but not as validation to a list.

Example D

With new data from A64:M76:

jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec
jan
jan-jan
jan-feb
jan-mar
jan-apr
jan-may
jan-jun
jan-jul
jan-aug
jan-sep
jan-oct
jan-nov
jan-dec
feb
feb-jan
feb-feb
feb-mar
feb-apr
feb-may
feb-jun
feb-jul
feb-aug
feb-sep
feb-oct
feb-nov
feb-dec
mar
mar-jan
mar-feb
mar-mar
mar-apr
mar-may
mar-jun
mar-jul
mar-aug
mar-sep
mar-oct
mar-nov
mar-dec
apr
apr-jan
apr-feb
apr-mar
apr-apr
apr-may
apr-jun
apr-jul
apr-aug
apr-sep
apr-oct
apr-nov
apr-dec
may
may-jan
may-feb
may-mar
may-apr
may-may
may-jun
may-jul
may-aug
may-sep
may-oct
may-nov
may-dec
jun
jun-jan
jun-feb
jun-mar
jun-apr
jun-may
jun-jun
jun-jul
jun-aug
jun-sep
jun-oct
jun-nov
jun-dec
jul
jul-jan
jul-feb
jul-mar
jul-apr
jul-may
jul-jun
jul-jul
jul-aug
jul-sep
jul-oct
jul-nov
jul-dec
aug
aug-jan
aug-feb
aug-mar
aug-apr
aug-may
aug-jun
aug-jul
aug-aug
aug-sep
aug-oct
aug-nov
aug-dec
sep
sep-jan
sep-feb
sep-mar
sep-apr
sep-may
sep-jun
sep-jul
sep-aug
sep-sep
sep-oct
sep-nov
sep-dec
oct
oct-jan
oct-feb
oct-mar
oct-apr
oct-may
oct-jun
oct-jul
oct-aug
oct-sep
oct-oct
oct-nov
oct-dec
nov
nov-jan
nov-feb
nov-mar
nov-apr
nov-may
nov-jun
nov-jul
nov-aug
nov-sep
nov-oct
nov-nov
nov-dec
dec
dec-jan
dec-feb
dec-mar
dec-apr
dec-may
dec-jun
dec-jul
dec-aug
dec-sep
dec-oct
dec-nov
dec-dec

Ranges = All!$A$65:$A$76&"-"& All!$B$64:$M$64

From B65:M76 I filled in the matrix formula:

{=Ranges}


You can download the file NamesText.zip through this link:

Een reactie plaatsen