We help IT Professionals succeed at work.

How to understand and apply the concept of arrays please? For a non programmer.

438 Views
Last Modified: 2012-02-20
Quote: "although programmers often use arrays you should not let that scare you". It does!

Hi, I have spent years not understanding arrays. I skim over them in the help. EE has a couple of threads, but they are by and for programmers I think.

I am an accountant, so do not have any programming understanding.

Can anyone explain to me what is an array so that I can finally grasp the idea?

I understand examples like this: match("b",{"a","b","c"},0) where the answer is 2. The abc are held in an array instead of in a list on the face of the spreadsheet: so here an array is just a list, albeit an invisible one.

Then we have choose(2,"a","b","c") where the answer is b.

Why is one using an array and the other not? Does an array provide a way of storing lists where there "should" only be one entry?

Is this a way of adding another dimension where there are (say) only two?

By dimension I mean for example a sales invoice list with customer names and I do not want to create another area for addresses (for whatever reason) can I store addresses in an array (subject to cell space limitations)? So I could practically store a lookup table in one cell?

Say I have a ledger item, an invoice and it is paid by multiple payments, can I store the payments history in one cell using an array, payment ref (i.d.) and amount as if it were an intermediate table between many to many making one to one connections? (don't let this make me look like a skilled programmer, very focussed bit of experience there).

Even if I have that right, and this process of asking the question is quite illuminating for me, what formulae tools are available to create, insert, append, edit the contents? vlookup for one.. and that's where my mind blocks.

A question I have had for many years.

I have zero vba or other languages skill. I might change that if I ever understand these arrays, especially ones that go beyond simple embedded lists.

Anthony
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
It seems like you questions is leaning toward Excel. Take a look at the following and see if it helps.

http://www.dummies.com/how-to/content/how-to-build-an-array-formula-in-excel-2010.html

Most use of arrays concern either programming or mathematical equations. Given you accounting background any reference to those will probably not help. If Excel is not where you are trying to use this, then post back.
Anthony MellorPrincipal
CERTIFIED EXPERT

Author

Commented:
yes Excel tag very much so.

Funnily enough I do have a spreadsheet I wrote 15(?) years ago that uses an array, but it was a one off where I followed instructions and it worked. All it does is replicate column A in one sheet into column B in another, but being an array cannot be tampered with by users.

This morning's reading is suggesting that application number one (for me) is simply prevention of user interfering with spreadsheets full of formulae.

For example I have one here full of lookups (which I am looking at using a query for but that's another story) and it strikes me that at the least, each column could be a vlookup entered as an array with ctrl-shift-enter (having selected down the column to the end of the data area) and if I can figure out how to make the vlookup column number dynamic based on the column it's in, could be copied across a contiguous range as one array, heck, the thing would be practiclaly bullet proof compared to now where it's (I see it as) highly vulnerable to user error, of both end users and authoring. I gather arrays are cpu intensive, the example I mention is only 108 rows long, though about 25 columns of lookups out of 50 columns where the rest is text entry.

Just reading page 175 of this (on Safari Books):
Master VISUALLY® Excel® 2010 By: Elaine Marmel

Very clear so far, but it's the application to accounting as opposed to as you say programming and maths, I have a feeling us accountants are missing a trick (at least this accountant is).

If I could see accounting applications of the idea I might have some chance of understanding, especially where they are used to store data, if that is a possible use. I've seen the likes of mathewspatrick and barryhoudini (amongst others) do black magic with arrays on here (and maybe you two!)

Anthony
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Anthony MellorPrincipal
CERTIFIED EXPERT

Author

Commented:
yes, I'm looking at this subject for two reasons, (1) it's chicken and egg, I suspect I could make effective use of them in my daily work as I work with arrays of numbers (accounting) and (2) more specifically whether they can be used as single cell storage units for data, such as for example names and addresses. So far it seems they occupy space to match their content, but can they hold data that does not use space?

and teylyn in your linked article is this comment : ", only numerical data types in that array or reference are summed: text data types are ignored" - very interesting in the context of your answer to me the other day about what generates value errors

Anthony
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
I'm not sure I follow. I don't think you can store a list of values in a single cell and then use it like an array. Well, you can, in a way. You can enter

={1,2,3,4,5}

into a cell. It will display a 1. You can then select the cell and the next four cells across, hit F2, confirm with Ctrl-Shift-Enter and you will see five cells, each with a single number, 1, 2, 3, 4 and 5.

Same goes for text:

={"Sam","Jim","Bob","Joe","Tom"}

Select five cells in a row and Ctrl-Shift-Enter this formula and you will see five cells with a name each.

For vertical distribution use a semicolon instead of the comma.
CERTIFIED EXPERT

Commented:
There is also a lot of good information on excel arrays on youtube.com.

Go there and search "excel array" and you will get a host of "how to" videos for arrays, including tips and tricks that may help you.
Anthony MellorPrincipal
CERTIFIED EXPERT

Author

Commented:
carlmd, that's a very good idea, thanks, I always forget youtube.

teylyn, you may have just put your finger on what it is I have (do) struggled with and it may be a misunderstanding  (by me). The fact you don't understand, is my clue.

Today I am seeing that arrays, both vector and matrix (learned those words this morning) occupy cells in the sheet that match their layout.

I have always thought (surmised) that arrays allow the same layout to be stored in ONE cell and referenced somehow. Choose() would be a simple illustration of data stored in one cell if the choose arguments are values/text. This is not to be confused with say a sum(array) which of course is in one cell.

So I was thinking a matrix array must be (capable of being)  a matrix of data that resides in only one cell.

{line1,line2,town,city;line1,line2,town,city;line1,line2,town,city}

would represent three addresses one per matrix row: all in one cell.

or
In A1
{100,paymentidwer;23, paymentidabd;250,paymentidxx}

representing three payments and their payment i.d. numbers; which when entered in one cell beside an invoice would total how much has been paid and by which payment id's.
So solving a 2D many-to-many problem I have had since 1989 (solved in a database with an intermediate table that makes them all one-to-one).

So, in short I am seeing arrays as potential single cell data repositories - and I am now wondering if this is what an "array constant" is. So using the A1 entry above, INDEX(A1,1,2) would return the entry at row 2 column 2 = paymentidxx

I wrote the para above with the help open for INDEX (array) function. The difference between Example 2 they give and my entry is that they put the array inside the index formula, whereas I want it located in a cell somewhere else.


Anthony
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
I'm afraid it does not work that way with multiple cell entries.

Using Index in the array version, you will point it to a range of cells, not a list of text entries in a single cell.

An array constant is something like {1,2,3} or {"Tom","Richard","Harry"}, as opposed to an array like A1:A3.

So, you can use

=Index(A1:A3,2)

If A2 has "Richard", then that will be returned. Or you can use an array constant, like

=Index({"Tom","Richard","Harry"},2)

which also returns "Richard". Array constants can be used if you don't want to create a helper list in a sheet and when you are happy to change the formula if the parameters change. For example you could have a lookup table for grades. 0 to3 is bad, 4-7 is medium and above 8 is good. You could create a table and use Vlookup

=Vlookup(A1,B1:C3,2,True)

Or you could use Lookup with an array constant like this

=Lookup(A1,{0,4,8},{"bad","medium","good"})

The Lookup formula uses two array constants, i.e. the contents of each array has been entered manually, and is not dependent on any other cells.

Even if the term array floats around here, these formulas are not array formulas. Many formulas work with arrays without being array formulas.

cheers,
Anthony MellorPrincipal
CERTIFIED EXPERT

Author

Commented:
I'm speaking of single cell arrays - array constants I gather, also named formulae?
Most Valuable Expert 2012
Top Expert 2012

Commented:
Anthony - it would work that way, if you stored the matrix of constants in a range name.

I'm not sure why you'd want to, as opposed to defining the matrix in the spreadsheet, itself, however...

=index(test,1,1) would return row1,column1 of range name test with the formula

test={"line1","line2","town","city";"line1","line2","town","city";"line1","line2","town","city"}

Dave
Anthony MellorPrincipal
CERTIFIED EXPERT

Author

Commented:
hi Dave, if each of the "line1" items were a number, could you add them up?

having said that, for payments I was hoping to store an array constant without putting it in a range name like a third dimension in a two dimensional sheet.

I've been reading "arrays" all day.. magical stuff.

question here EE Q


reason for single cell storage desire is say I have 2,000 invoices and each one is paid with one two or three payments, I want to store the payments history in the cell next door while still being able to do maths with it.

reason for storing arrays as names is to take smaller lookup tables (say tax rates etc) and copy them from my master file into a safe place where users don't need to see them; so the spreadsheet appears less complex (while somewhat ironically being more so).
I suppose it's sort of akin to storing custom sort orders... kinda, somewhere to put them out of the way while still being able to use them. To achieve this I would have the array in a sheet just as you suggest and then pick up the table as an array, edit calc and copy the results into the names ref to box. If I have explained that right, only learned it today.

- anthony
Most Valuable Expert 2012
Top Expert 2012

Commented:
Anthony - why not try it out?

Create a range name called test with the following two rows, 7 columns:

test={5,4,3,2,3,4,5;1,2,3,4,5,6,7}

=SUM(INDEX(1,test)) sums the first row, which should equate to 26
=SUM(INDEX(2,test)) sums the first row, which should equate to 28

Cheers,

Dave
Anthony MellorPrincipal
CERTIFIED EXPERT

Author

Commented:
HI Dave, I see how that works, what about summing columns?
i.e 5 + 1 = 6
falling asleep over my desk here, see you tomorrow :-)
Anthony
Most Valuable Expert 2012
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Anthony MellorPrincipal
CERTIFIED EXPERT

Author

Commented:
Hi Dave, here's my question about that array question
Thanks for your patient interest.
Anthony
Most Valuable Expert 2012
Top Expert 2012

Commented:
Glad you got it sorted.

Enjoy!

Dave

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.