+ Reply to Thread
Results 1 to 5 of 5

Average based on value if ref. exist in matrix

  1. #1
    Registered User
    Join Date
    09-19-2020
    Location
    Stockholm
    MS-Off Ver
    MS365 64-bit
    Posts
    7

    Average based on value if ref. exist in matrix

    Hi,

    It's probably easier than I think but I'm stucked with this formula to get an average of the value if the daily ref. exist in the matrix on any of the days before.

    IF B19 (light blue cell) exist in G:L (red cells) any day it should return the value from that day F2:18 (yellow cells) and calculate an average in the green cell.

    Note 1. Value in matrix are uniqe on row-level which means C20C (Cell B19) only exist maximum once per row between cell G:L.

    Note 2. Date will grow. Today we found C20C (Cell B19) in Matrix J16 and H4. In future it might appear in +100 cells. Every new date should calculate an average based on all days before this date (if the reference appear in the matrix).

    Hope it is clear what I try to do.


    Excel.jpg

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,161

    Re: Average based on value if ref. exist in matrix

    A spreadsheet loaded would help - more than a image - see the yellow banner at the top of the thread

    I suspect there is a better way BUT
    =SUMPRODUCT(($G$2:$L$100=$B19)*($F$2:$F$100))/COUNTIF($G$2:$L$100,$B19)

    Change the 100 to the max number of rows the data is likely to grow to

    A rough mockup spreadsheet with limited data in a sample spreadsheet
    Attached Files Attached Files
    Last edited by etaf; 06-26-2021 at 02:09 AM.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    09-19-2020
    Location
    Stockholm
    MS-Off Ver
    MS365 64-bit
    Posts
    7

    Re: Average based on value if ref. exist in matrix

    Thank you!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,521

    Re: Average based on value if ref. exist in matrix

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

    I doubt your version is Excel 2009 - I suspect it's MS365. Please update your forum profile. 2009 is just a release number.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    2,034

    Re: Average based on value if ref. exist in matrix

    Try array formula

    HTML Code: 
    =AVERAGE(IF(G2:L19=B19,F2:F19))

+ 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. [SOLVED] Average of matrix elements using row and column conditions
    By Luis Ah-Hoy Jr. in forum Excel General
    Replies: 3
    Last Post: 06-09-2017, 10:42 AM
  2. [SOLVED] Check First IF Sheet Is Exist With The Name Of Cell K7 If exist Then run the code
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-18-2015, 10:08 AM
  3. User Form Matrix Cell Selection - Does it exist?
    By FMontgomery in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-15-2013, 05:41 PM
  4. Replies: 1
    Last Post: 03-12-2009, 06:33 AM
  5. Copy Worksheet to a new Workbook creating if it doesn't exist and add more Worksheets if it does exist
    By pete.bastin@btopenworld.com in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-18-2006, 01:10 PM
  6. Replies: 3
    Last Post: 03-11-2006, 02:50 PM
  7. Replies: 0
    Last Post: 03-10-2006, 05:55 AM

Tags for this Thread

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