# Average based on value if ref. exist in matrix

1. ## 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. ## 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

Thank you!

4. ## 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.

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

Try array formula

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

##### 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1