Results 1 to 4 of 4

Using Average function with Array

Threaded View

  1. #1
    Registered User
    Join Date
    09-03-2014
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    2

    Using Average function with Array

    I have data in a worksheet that represents the monthly return on various stocks. A mock up of the data is attached and has:
    A: stock name
    B-M: monthly return % for Jan - Dec

    I have created an array function using FVschedule that I have placed in column P:
    {=FVSCHEDULE(1,OFFSET(B2:B10,ROW(B2:B10)-ROW($B$2),0,1,12))}

    This calculates the annual compounded return for each stock (I compare it to the non-array calc in Col O)

    I am able to calculate the average return across the portfolio manually using AVERAGE(P2:P10), but am unsure how to do this in one cell using the array.

    I tried: {=AVERAGE(FVSCHEDULE(1,OFFSET(B2:B10,ROW(B2:B10)-ROW($B$2),0,1,12)))}

    but get a #VALUE result. Can anyone help with what I am doing wrong? Thanks in advance.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Array Function: Using an array function to calculate loan outstanding
    By akshaythakker in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-30-2016, 05:33 PM
  2. Replies: 3
    Last Post: 04-02-2016, 08:16 PM
  3. Table Array for AVERAGE function
    By albatross32 in forum Excel General
    Replies: 3
    Last Post: 04-23-2010, 05:19 AM
  4. Average & Min Function Problem in Array Formula
    By mubashir aziz in forum Excel General
    Replies: 8
    Last Post: 05-20-2009, 02:07 AM
  5. #DIV/0! error in Average If Array function
    By ren1104 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-28-2007, 02:24 PM
  6. [SOLVED] Error Handling #N/A with AVERAGE Function - Average of values in Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. [SOLVED] Error Handling #N/A with AVERAGE Function - Average of values in Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

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