troubleshooting Question

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

Avatar of Anthony Mellor
Anthony MellorFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft ExcelSpreadsheets
17 Comments3 Solutions445 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 17 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros