# Simple visual example of how Array Formulas work

1. ## Simple visual example of how Array Formulas work

I recently started understanding array formulas a little better and wanted to explain in a visual way, because I would have loved to see something like this when I was trying to figure it out myself. Hopefully this helps someone.

This file is showing how to find the location of a given value. In this case, we are looking for the location of the largest number in a grid of numbers. I left the numbers as Randomly Generated between 1000 and 9999, so you can see how it works.

Each section contains the formula from the previous section, with modifications made and explained.

Feel free to modify any part of this to see what happens, make sure to use Control+ Shift + Enter to place the formulas after modifying.

Enjoy!

2. ## Re: Simple visual example for Array Formulas

Here is another example of array formulas, this time I am looking for the row number of the lowest instance of the number 5.

I used range 3:17 in the ROW() formula this time, because I want to know what row it is on relative to the rest of the sheet, not what row number it is on in the table

array_formulas2.xlsx

3. ## Re: Simple visual example of how Array Formulas work

This is helpful to understand complicated array formula.
Thanks.
PN

4. ## Re: Simple visual example of how Array Formulas work

. .
. Well explained.
. Hope you do some more
. I just started doing some to accompany the Threads I managed to answer using Array Formulas.

Alan

EDIT: Just after lurking through this Thread... I saw by coincidence and managed to answer a Thread which was needing the stuff explained by Speshul
http://www.excelforum.com/excel-new-...g-3-cells.html
.. just goes to show how useful it is to share stuff...

... ( and that it is good to "Google" and "Lurk" first.........

5. ## Re: Simple visual example of how Array Formulas work

being new at this is there an easy way to learn formula

6. ## Re: Simple visual example of how Array Formulas work

Originally Posted by andy1494
being new at this is there an easy way to learn formula
Hi Andy,
… Someone with a lot more experience would be better to answer you here… I guess..
. I have been learning VBA part time for about a year. For a couple of weeks recently I had a quick delve into array formulas ( “CSE” stuff )….
. I found it a bit of a weird one, was a bit skeptical about them…and so will probably stick mostly to VBA instead.
. The best way to learn as always is practice, - try to follow a few Threads and even have a go at answering some Threads as I did…..
. As for an easy way, I would recommend some You Tube videos..check out the ones I referenced around about Post #19 here..
http://www.mrexcel.com/forum/excel-q...-column-2.html
… a couple of play lists referenced there I downloaded and found “easy” to follow. Even some experienced Formula People advised me to check out those video play lists, saying they had got a large amount of their formula knowledge from that video series..
. Alan

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1