Home For AAT student members AQ 2016 Advanced Diploma in Accounting Spreadsheets for Accounting
Current updates regarding coronavirus (Covid-19) and the precautions AAT are taking will be continually updated on the below page.

Please check this link for the latest updates:
We hope you are all safe and well and if you need us we will be here. 💚


Level 3 spreadsheeets

Could someone please tell me what an array formula is? I know, it is not rocket science; but, a good explanation would benefit my understanding.

Comments

  • KoopaCooperKoopaCooper London, UKMAAT, AATQB Posts: 227
    I had to look this up...because my own version of Excel (Excel 2000) seriously predates this particular function being introduced! xD

    Basically, an array formula is a single formula that works on an entire set of cells, and and collects a set of formulae together.

    Say you have two columns of data in your spreadsheet - column A is quantity, column B is unit price.

    Normally, to work out totals of each item, the cells in column C would have the formulae:

    =A1*B1 (for C1)
    =A2*B2 (for C2)
    =A3*B3 (for C3)
    ...and so on. Let's say there were 100 different items. The last one would be =A100*B100

    But instead, an array formula would collect all of these into one, as follows:

    =A1:A100*B1:B100

    This formulae is telling Excel to mutiply the first cell in the first range (A1) by the first cell in the second range (B1), then doing the same with the second cell in each (A2*B2) and so on, down to 100.

    I hope that helps. :)
    Accounts Executive, ғɪᴀʙ ᴍᴀᴀᴛ
    QBO & Xero certified advisor

    AAT
    Level 2 Certificate in Accounting - 17 Jun 2015
    Level 3 Diploma in Accounting - 22 Jan 2016
    Level 4 Diploma in Accounting - 19 Dec 2017
    MAAT - 27 Mar 2018

    ACCA
    Advanced Diploma in Accounting and Business...
    F4 Corporate and Business Law - 4 Jun 2019
    F5 Performance Management - 15 July 2019
    F6 Taxation (United Kingdom) - 3 Dec 2019
    F8 Audit and Assurance - 2 Mar 2020
    Claudine71
Sign In or Register to comment.