+ Reply to Thread
Results 1 to 3 of 3

AVERAGEIF Formula on an array of data

  1. #1
    Registered User
    Join Date
    04-18-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    2

    AVERAGEIF Formula on an array of data

    Hi there, I would really appreciate some help. I am working in excel 2010.

    My Data is in Columns as below (this is a snapshot only, my spreadsheet is variable in size, but normally about 250 columns):

    C1 C2 C3 C4 C5
    Peer 7.0 6.0 6.0 6.0
    Peer 7.0 6.0 7.0 7.0
    Peer 1.0 6.0 5.0 -
    Manager 7.0 7.0 7.0 7.0
    Peer 7.0 6.0 6.0 6.0
    Peer 6.0 6.0 5.0 6.0
    Peer 6.0 6.0 6.0 -
    Manager 7.0 6.0 4.0 5.0
    Report 6.0 6.0 7.0 6.0

    I need to average the data in C2 - C5 using the value in C1 as the criteria. So for instance, an average of all manager ratings from Row 4 and 8 (ie. answer 6.3)

    I tried formula [=ROUND(AVERAGEIF(R2C5:R[-9]C5,"MANAGER",R2C:R[-9]C[8]),1)]. However, this returns an average from C2 only.

    I can write a loooong formula to fix my problem but I would like to avoid this if possible. I can't use an array formula as I am using a data manipulation program that writes these formulas in to excel for me.

    My only other option is to restructure my spreadsheet to avoid this. Before I start down this path, does any one have any suggestions?

    Many thanks in advance.

    ST

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: AVERAGEIF Formula on an array of data

    Hello Shoto, try this.

    Consider the data is in A2:E10. A15:A17 is names, like Peer, Manager & Report.

    In B15, then copy down

    Please Login or Register  to view this content.
    COLUMN(B$2:E$2)^0 used here to equalize the SUMPRODUCT dimension to avoid any multiplication by text. So adjust this accordingly.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    04-18-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: AVERAGEIF Formula on an array of data

    Hi Haseeb

    Many thanks for your suggestion!

    At first test it seems to work, although I need to do some homework to fully understand what it is doing!

    I will run some trials tonight, hopefully it is the solution I am looking for.

    Much much much appreciated.

    ST

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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