Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

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

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

=IF(COUNTIF(B$2:B3,B3)<=CO

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

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

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

=AVERAGE(IF((B$3:B$24=B23)

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

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

Thanks/Ian

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)*

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

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

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(

Brad

Average-match-names-2Q28973208.xlsx

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

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

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

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

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,

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

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

I will spend time tomorrow looking at the suggestions you guys have kindly put forward.

Many thanks for your contributions

Ian

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

Brad

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!

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

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

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

Having some computer issues right now an error 5 problem.

Will come back to you a bit later

Ian

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

Thanks

Ian

I have leaned towards Neeraj's solution as it appears the simplest for

a novice like myself.

Cheers

Ian

I guess, you don't need to tweak the code for any reason.

Just treat that function like a normal inbuilt function.

Rob, seems like I need to learn pivot tables.

Thanks Brad good pick up on that code correction

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 =

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.

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

Open in new window

and copy down.Average-match-names.xlsm