+ Reply to Thread
Results 1 to 6 of 6

Simple visual example of how Array Formulas work

  1. #1
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    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!
    Attached Files Attached Files
    Last edited by Speshul; 07-16-2014 at 04:36 PM.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    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. #3
    Registered User
    Join Date
    01-17-2015
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    85

    Re: Simple visual example of how Array Formulas work

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

  4. #4
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    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.........
    Last edited by Doc.AElstein; 06-13-2015 at 07:02 PM. Reason: ..Just answered a Thread with what I learnt from this!!!!!
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  5. #5
    Registered User
    Join Date
    06-14-2015
    Location
    england
    MS-Off Ver
    2013
    Posts
    12

    Re: Simple visual example of how Array Formulas work

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

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Simple visual example of how Array Formulas work

    Quote Originally Posted by andy1494 View Post
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. First Crack at a simple Visual Basic Execute FAIL!!!!!!!
    By amaro5211 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-14-2013, 06:06 PM
  2. Replies: 1
    Last Post: 02-08-2013, 09:24 AM
  3. A simple game in Visual Basic
    By maqre in forum Excel General
    Replies: 4
    Last Post: 10-26-2012, 01:44 AM
  4. ARRAY in visual basic
    By JohnSeito in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-24-2008, 01:35 AM
  5. [SOLVED] Problem with a very simple code in Visual basic
    By Vepa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-25-2006, 11:45 AM

Bookmarks

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