# find average values from matched names

Hi,
I am seeking a formula that finds the average of last 6 values from matching names.
Please see attached sheet and place formula in cells D23:D24
Many thanks
Ian
Average-match-names.xlsx
###### Who is Participating?

Excel & VBA ExpertCommented:
Hi Ian,

You may also use a User Defined Function to get the desired output.
Please refer to the attached, where I have placed the function in column E.
You may try the custom function like this....
In E3
``````=AverageLastSixValues(B3,B\$3:B3,C\$3:C3)
``````
and copy down.
Average-match-names.xlsm
0

Finance AnalystCommented:
I have been able to solve this for you with a Pivot Table.

=IF(COUNTIF(B\$2:B3,B3)<=COUNTIF(\$B\$2:\$B\$24,B3)-6,"","OK")

where the B references refer to your names.

Then build a Pivot Table on your data including all 3 columns:
Count above - Page Filter, apply filter to show only OK.
Items - Values and set this in Value settings to show Average

Thanks
Rob H
0

retiredAuthor Commented:
Thanks Rob, however I have not worked with pivot tables so my knowledge of them is zero.
I'd much prefer a formula if once could be devised.
I will be working with large data sets adding to them on a daily basis.
Any ideas on a formula ?
Ian
0

Finance AnalystCommented:
Pivots are a great way of summarising large data sets. Newer versions of Excel can also have Power Pivot which is even more powerful (my knowledge of Power Pivot is also zero other than knowing it exists).

If you set your data list to a Table then once the Pivot Table is built it will just need to be refreshed, the formula for counting the latest entries will copy down as the table expands.

See attached.

As you add more detail to the bottom of the table on Data sheet, the table area will expand. Then go to the pivot, right click and choose Refresh.

If your set on a formula based approach, then you will still need a Count formula but you can then use the AVERAGEIFS function, also in attached.

Thanks
Rob H
Average-match-names.xlsx
0

Commented:
If you don't want to insert an auxiliary column, you can copy down this array-entered formula in cell D23:
=AVERAGE(IF((B\$3:B\$24=B23)*(ROW(B\$3:B\$24)>=AGGREGATE(14,4,(B\$3:B\$24=B23)*ROW(B\$3:B\$24),6)),C\$3:C\$24,""))

To array-enter a formula:
1. Click in the formula bar
2. Hold the Control and Shift keys down
3. Hit Enter, then release all three keys
4. Excel should respond by putting curly braces { } surrounding the formula.

If you see #VALUE! as the result of the formula (and the curly braces are missing), repeat steps 1 through 4
0

retiredAuthor Commented:
Hi Byundt,

I have attached the results from your formula however they
don't match the real figures.
As a reminder what I am trying to achieve is the average of last 6 items including current row.
I will eventually be usuing up to 100,000 rows and would need to copy down the formula
however I could not copy an array. The array may not be suitable for my requirements.
Many thanks
Ian
Average-match-names.xlsx
0

retiredAuthor Commented:
Hi Rob, Sorry, I can't follow your last suggestion. Is it possible for you to incorporate the COUNT in your formula ?
Thanks/Ian
0

Commented:
racepro,
You changed the rules for the formula. It appears that you want to consider only the six most recent values taken from that row and above. If a name occurs lower on the list, its associated value does not figure into the average.

With that understanding, I array-entered the following formula in cell E26 and copied up:
=AVERAGE(IF((B\$3:B26=B26)*(ROW(B\$3:B26)>=AGGREGATE(14,4,(B\$3:B26=B26)*ROW(B\$3:B26),6)),C\$3:C26,""))

Given that you may have 100,000 rows, an array formula is going to be time-consuming. I have an idea for some auxiliary columns that should speed things up. I'll post back in a bit.

Average-match-names-2Q28973208.xlsx
0

retiredAuthor Commented:
Sorry for any misunderstanding but I did mention "last 6 values" in my initial post.
Perhaps you understood 6 from the bottom rather than 6 from current row.
Thanks for coming up with the formula but I still unsure how to copy the rows
without getting the array error message.
Look forward to your auxiliary columns
Cheers
Ian
0

Commented:
Ian,
I added three auxiliary columns. One keeps track of how many times a name has occurred. Another keeps track of the cumulative points. And the third is a concatenation of the name & count.
=COUNTIF(B\$3:B3,B3)         count of names in D3, copied down
=SUMIF(B\$3:B3,B3,C\$3:C3)   sum of Items in E3, copied down
=B3 & " " & D3        concatenation of name & count in F3, copied down

If the count is 6 or less, then the average is the running sum of Items divided by the count. If the count is more than 6, then you need to subtract the (n-6)th most recent running sum of items from the running sum in the current row.
=IF(D3<=6,E3/D3,(E3-INDEX(E\$3:E3,MATCH(B3&" "&(D3-6),F\$3:F3,0)))/6)        average of last 6 values in H3, copied down

Average-match-names-2Q28973208.xlsx
0

Finance AnalystCommented:
Brad, my suggestion does that but with only 1 helper column, the count. Then AVERAGEIFS based on name and count.

For 100,000 rows I would highly recommend using a pivot or maybe even power pivot.
0

Commented:
Rob,
The AVERAGEIFS formula returns the same answer for a Name no matter what row it is on. But Ian is looking for the average of the six most recent Items from that row and higher. In other words, there should be a different answer in your sample workbook for cells F21 and F23 (both of which refer to P SMITH). That blows the logic of the OK column up because a cell might be blank when testing the 9th occurrence of a name (way down the list), but needs to be OK if you test an occurrence of the name somewhere in the middle of the table.

For the same reason, you can't use a PivotTable or PowerPivot.

0

retiredAuthor Commented:
Hi Neeraj,

How come your formula works ? There is no matching function. Is this some sort of magic ?

Ian
0

Excel & VBA ExpertCommented:
Hi Ian,

No. It's not a magic. As I said it is a user defined function and you can view the code on Module1.

In the following formula....
=AverageLastSixValues(B3,B\$3:B3,C\$3:C3)
B3 is the lookup value (name in your case), B\$3:B3 is the lookup range (range where names exist) and C\$3:C3 is the value range (range where value exist).

Also you can copy the formula from the bottom cell in upward direction as well i.e. drag the formula up. So if you have a large data set and you are interested to get the average for last few names only in the data set, you may place the formula in the last cell first and then drag the formula up.
e.g. you can place the following formula in E26 and then drag the formula up.
=AverageLastSixValues(B26,B\$3:B26,C\$3:C26)

Also in the function I have used two separate ranges (instead of one range like B\$3:C26) one for column B and another for column C keeping in mind that your values column may not be adjacent to the name column. So if your name column is column B and the values are in column say col. G, you only need to pass two different ranges in the formula.

Neeraj
0

retiredAuthor Commented:
Neeraj,
Oh I see, you are using VB and like pivot tables I am however inexperienced.
I have looked at the code.
If I was to do similar for other columns is it easy to do ?
I think it is high time I spent time learning more about Excel especially pivot tables and VBA.

Ian
0

retiredAuthor Commented:
Hi All,
I will spend time tomorrow looking at the suggestions you guys have kindly put forward.
Ian
0

Commented:
Neeraj,
Nicely done! Very efficient to look up from the bottom.

One suggestion: in the next to last statement where you calculate the average, divide by j instead of a constant 6. Otherwise you get spurious averages at the top of the table when there are fewer than 6 instances of a name.
``````AverageLastSixValues = total / j        'Use j instead of 6 as divisor
``````

2

Excel & VBA ExpertCommented:
@Ian
As I said you can use any columns in the formula and you don't need to change the code.

Good catch. It is better not to divide by a constant.
0

retiredAuthor Commented:
Hi Neeraj,

You say I can copy to other columns and not necessary to modify code, how does that work ?
I have attached two more columns but formula produces zeros.
Pls have a look at attachment
Thanks
Ian
Average-match-names-Neeraj.xlsm
0

Commented:
Ian,
I am getting funny errors with the workbook you posted. One error message complains about undefined user-specified type in the VBA. The other error message complains about a Circular Reference. The problem is, there aren't either type of error in the workbook.

I was able to overcome the Circular Reference error message by turning on Iterations (check the box for "Enable iterative operation" in the File...Options...Formulas menu item). This step should not be necessary, but perhaps there is some corruption in the workbook/

Note also that you need to fix the next to last statement in the Neeraj' function to divide by j rather than 6.

0

Excel & VBA ExpertCommented:
Hi Ian,

I have tweaked the code to avoid the circular reference being created by the function.
Please refer to the attached for more details.

Neeraj
Average-match-names-Neeraj.xlsm
0

Finance AnalystCommented:
Ian, just been looking to see why my answer for J WILSON was different to yours, your sample was including 7 cells in the average. If you remove the 7th (C7) I get the same answer with the AVERAGEIFS function.
0

retiredAuthor Commented:
OK Rob, thanks
Having some computer issues right now an error 5 problem.
Will come back to you a bit later
Ian
0

retiredAuthor Commented:
Hi Neeraj,

If I were to modify the number of rows would it be a simple matter of changing the code ?
Thanks
Ian
0

Commented:
You would change the worksheet formula that calls Neeraj' code to handle a different number of rows. The code itself does not need to be changed.
0

retiredAuthor Commented:
Thank you guys for your help.
I have leaned towards Neeraj's solution as it appears the simplest for
a novice like myself.
Cheers
Ian
0

Excel & VBA ExpertCommented:
Hi Ian,

I guess, you don't need to tweak the code for any reason.
Just treat that function like a normal inbuilt function.
0

retiredAuthor Commented:
Great result Neeraj thanks
Rob, seems like I need to learn pivot tables.
Thanks Brad good pick up on that code correction
0

retiredAuthor Commented:
Neeraj,
If I don't need to tweak the code how will it calculate say 50 rows instead of 6 ?
Ian
0

Excel & VBA ExpertCommented:
You're welcome Ian! Glad we could help.
0

Excel & VBA ExpertCommented:
If I don't need to tweak the code how will it calculate say 50 rows instead of 6 ?
Now I understand what did you mean.
Tweak the number in the following line of code as per your requirement.
If j = 6 Then Exit For
0

retiredAuthor Commented:
Thanks, I thought so ........ I knew that wasn't magic :)
Ian
0

Excel & VBA ExpertCommented:
Haha
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.