+ Reply to Thread
Results 1 to 4 of 4

Using Average function with Array

  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

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Using Average function with Array

    sphillpo welcome to the forum.

    Not really, but OFFSET appears to be array resistant.

    Try array entering this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The N function has coercion properties. I use it when the expected array is numeric ... T when it is text. I get 0.964081 at my end.

    Is that what you expect?
    Last edited by FlameRetired; 08-17-2017 at 07:20 PM.
    Dave

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

    Re: Using Average function with Array

    Hi FlameRetired

    Thanks for the response. I also get the 0.964081, if I try that, but that is not the correct answer. You can see where I calculate the average in the cells in O12 and P12 that the result I am looking for is 1.163728034

    Any other ideas. It seems strange when the offset has already worked for the array that I put in column P.

    regards

    Steve

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Using Average function with Array

    The array formula is attempting to average the entire set of FVSCHEDULE. I am not familiar with that function or its relevance here. In O:P it is calculating FVSCHEDULE on each separate row. In the array formula it is applying to the whole array of data at once.

    Further AVERAGE contributes nothing to that formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    returns exactly the same value.

    Sorry I can not advise you any further. I don't know enough about the field to intuit the overall goals.

    PS I've called for help from the community.
    Last edited by FlameRetired; 08-17-2017 at 09:35 PM.

+ 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. 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. 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. 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