Solved

find average values from matched names

Posted on 2016-09-29
33
59 Views
Last Modified: 2016-09-30
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
Comment
Question by:racepro
  • 14
  • 8
  • 7
  • +1
33 Comments
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
I have been able to solve this for you with a Pivot Table.

Add headers to your columns and alongside your data put a column with this formula:

=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.
Names - Row header
Items - Values and set this in Value settings to show Average

Thanks
Rob H
0
 

Author Comment

by:racepro
Comment Utility
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 31

Assisted Solution

by:Rob Henson
Rob Henson earned 100 total points
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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

by:racepro
Comment Utility
Hi Byundt,

Thanks for your formula.
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.
Please check spreadsheet
Many thanks
Ian
Average-match-names.xlsx
0
 

Author Comment

by:racepro
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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.

Brad
Average-match-names-2Q28973208.xlsx
0
 

Author Comment

by:racepro
Comment Utility
Hi Brad,
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 80

Assisted Solution

by:byundt
byundt earned 100 total points
Comment Utility
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

Brad
Average-match-names-2Q28973208.xlsx
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 300 total points
Comment Utility
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)

Open in new window

and copy down.
Average-match-names.xlsm
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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.

Brad
0
 

Author Comment

by:racepro
Comment Utility
Hi Neeraj,

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

Ian
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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

by:racepro
Comment Utility
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

by:racepro
Comment Utility
Hi All,
I will spend time tomorrow looking at the suggestions you guys have kindly put forward.
Many thanks for your contributions
Ian
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 80

Expert Comment

by:byundt
Comment Utility
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

Open in new window


Brad
2
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
@Ian
As I said you can use any columns in the formula and you don't need to change the code.

@Brad
Good catch. It is better not to divide by a constant.
Thanks for the tweak Brad!
0
 

Author Comment

by:racepro
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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.

Brad
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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

by:racepro
Comment Utility
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

by:racepro
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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

by:racepro
Comment Utility
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 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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

by:racepro
Comment Utility
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

by:racepro
Comment Utility
Neeraj,
If I don't need to tweak the code how will it calculate say 50 rows instead of 6 ?
Ian
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
You're welcome Ian! Glad we could help.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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

by:racepro
Comment Utility
Thanks, I thought so ........ I knew that wasn't magic :)
Ian
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
Haha
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now