Solved

# find average values from matched names

Posted on 2016-09-29
79 Views
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
0
Question by:racepro
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 14
• 8
• 7
• +1

LVL 33

Expert Comment

ID: 41821282
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

Author Comment

ID: 41821299
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

LVL 33

Assisted Solution

Rob Henson earned 100 total points
ID: 41821312
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

LVL 81

Expert Comment

ID: 41821715
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

Author Comment

ID: 41822168
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

Author Comment

ID: 41822173
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

LVL 81

Expert Comment

ID: 41822278
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

Author Comment

ID: 41822315
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

LVL 81

Assisted Solution

byundt earned 100 total points
ID: 41822364
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

LVL 31

Accepted Solution

Subodh Tiwari (Neeraj) earned 300 total points
ID: 41822375
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

LVL 33

Expert Comment

ID: 41822376
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

LVL 81

Expert Comment

ID: 41822412
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

Author Comment

ID: 41822465
Hi Neeraj,

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

Ian
0

LVL 31

Expert Comment

ID: 41822493
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

Author Comment

ID: 41822509
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

Author Comment

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

LVL 81

Expert Comment

ID: 41822521
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

LVL 31

Expert Comment

ID: 41822527
@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

Author Comment

ID: 41822583
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

LVL 81

Expert Comment

ID: 41822665
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

LVL 31

Expert Comment

ID: 41822865
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

LVL 33

Expert Comment

ID: 41823357
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

Author Comment

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

Author Comment

ID: 41823967
Hi Neeraj,

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

LVL 81

Expert Comment

ID: 41823972
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

Author Comment

ID: 41823973
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

LVL 31

Expert Comment

ID: 41823975
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

Author Closing Comment

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

Author Comment

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

LVL 31

Expert Comment

ID: 41824007
You're welcome Ian! Glad we could help.
0

LVL 31

Expert Comment

ID: 41824014
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

Author Comment

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

LVL 31

Expert Comment

ID: 41824096
Haha
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.