+ Post New Thread
Results 1 to 2 of 2
Office Software Thread, Excel - Averge value of a subset of results in Technical; Calling all Excel gurus (or possibly just anybody with more nouse than I)... Range_1 Range_2 a...............20 b...............10 c...............10 a...............10 c...............30 ...
  1. #1

    Join Date
    Nov 2006
    Location
    Lancashire
    Posts
    95
    Thank Post
    34
    Thanked 1 Time in 1 Post
    Rep Power
    0

    Excel - Averge value of a subset of results

    Calling all Excel gurus (or possibly just anybody with more nouse than I)...

    Range_1 Range_2
    a...............20
    b...............10
    c...............10
    a...............10
    c...............30
    b...............20

    I would like a formula that finds the average of results in 'Range_2' for all rows in 'Range_1' that equal "a".

    I thought something like:

    =AVERAGE(SUMPRODUCT((Range_1="a")*(Range_2))

    However, this returns the sumproduct but not average. Any help would be very much welcomed.

    Thanks a lot.

    Ben

  2. #2

    Join Date
    Nov 2006
    Location
    Lancashire
    Posts
    95
    Thank Post
    34
    Thanked 1 Time in 1 Post
    Rep Power
    0
    =AVERAGE(SUMPRODUCT((Range_1="a")*(Range_2))
    OK,

    This works:

    =SUMPRODUCT((Range_1="A")*(Range_2)/COUNTIF(Range_1,"A")

    but is there a more elegant way?

    Thanks all,

    Ben

SHARE:
+ Post New Thread

Similar Threads

  1. Daily results
    By Geek_of_HeathMount in forum Coding
    Replies: 4
    Last Post: 19th May 2009, 01:50 PM
  2. Search Results
    By rush_tech in forum Web Development
    Replies: 4
    Last Post: 8th December 2008, 02:13 PM
  3. F1 Fantasy Results
    By russdev in forum General Chat
    Replies: 8
    Last Post: 25th March 2008, 06:35 PM
  4. Results
    By ctbjs in forum ICT KS3 SATS Tests
    Replies: 21
    Last Post: 9th July 2007, 05:57 AM
  5. Test results
    By beeswax in forum ICT KS3 SATS Tests
    Replies: 21
    Last Post: 4th July 2006, 12:35 PM

Thread Information

Users Browsing this Thread

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •