Formula to show Average of the last 'N' values in a row.

[XLHell]

Hi,

I need a formula to show the Average of the last 'N' values in a row. The row will be dynamically expanding. It is a database where the first column is a list of names. Each successive column represents an event where every person might be assigned a number. I want a formula to show for each person the average of their last 'N' non-blank values.

Can anyone help me out?

Edit: In the sample workbook what is needed is a formula in D2 to show the average of the last "N" values in the row starting from E2.

[Pepe Le Mokko]

Hi
as members tailor their answer to the Excel version, please add your version to your profile as it only states "Excel" ( which is obvious here). Thx

[XLHell]

Hi, I've done this thanks.

[AliGW]

Last 'N' in a ROW or a COLUMN???

Where in the sample workbook have you provided some expected results?

[XLHell]

I'm not sure what formula to use. I want the results to be in Column 'D' which currently has a simple average formula because I don't know how to get the formula I want.

I want the average of the last 'N' values in the row.

This is a database that will dynamically expand and will have thousands of rows and columns.

The first column is the names of the people. The following columns (barring sample, Count, average etc) will be events where some of the people will be assigned a numerical score. I want the average of the last 'X' scores that have been assigned, for each person.

[AliGW]

Please mock up some expected results in your workbook. It will be much easier for us if the sample data is not all number 10!!!

You do not need to know what formula to use, but surely you know what results you are expecting in the sample file? I just want you to add 10-15 rows in the workbook. Understand?

[XLHell]

Thanks so much for the help, and apologies for being a bit slow. I've added several rows now. If I want the average of the last 20 values (this should be 30.95) I want to know the formula to input.

[AliGW]

Firstly, you have removed the workbook and not replaced it - please reshare the amended workbook.

Secondly, there is NO NEED to keep quoting the post before your own - plaese stop doing so, as it is a waste of space. Thanks.

[XLHell]

I know I'm trying to get it back, it shows as uploaded but keeps disappearing as soon as I close the window.

[XLHell]

Sorry I forgot to save the changes. The correct version is up now.

[AliGW]

OK - thanks. This will help, but I have run out of time (need to get out into the garden). Someone else will advise.

[XLHell]

Bumping this since I haven't received any suggestions. Can someone help me out please?

[menem]

I don't understand what you want.

Plese specify which cell you want the result and what is the result in that cell.


Regards.

Note : You're already put AVERAGE() in D2 , so I think D2 is the cell you want the result to display.
But I don't know what is the value in D2 you want.

[XLHell]

The value I want is the average of the last 'N' non-blank values in the row.

For example if I want the average of the last 20 values, it should be 30.95. I don't know what formula to use to get that result.

[menem]

the last 'N' non-blank

I'm still don't understand , AVERAGE() calculate from non-blank cells already.
Please specific the result value in your file.

Regards.

[AliGW]

In your sample file, in the first row of sample data, please show us how you calculated 30.95. Highlight the cells you used for this. I cannot get the last 20 non-blank cells to calculate that number however hard I try.

[menem]

Hmmm,

Please try

D2
=AVERAGE(INDEX(2:2,1,IFERROR(AGGREGATE(14,6,COLUMN($E2:INDEX($E2:$XFD2,1,AGGREGATE(14,6,COLUMN(E2:XFD2)/(E2:XFD2<>""),1)-COLUMN(E2)+1))/($E2:INDEX($E2:$XFD2,1,AGGREGATE(14,6,COLUMN(E2:XFD2)/(E2:XFD2<>""),1)-COLUMN(E2)+1)=""),1)+1,COLUMN(E2))):
INDEX(2:2,1,AGGREGATE(14,6,COLUMN(E2:XFD2)/(E2:XFD2<>""),1)))

Regards,

[AliGW]

That returns 19.75 - the OP is looking for 30.95.

My best guess is this:

=AVERAGE(INDEX(E2:AZZ2,LARGE(COLUMN(E2:AZZ2)*(E2:AZZ2<>""),20)):$AZZ2)

and it yields 25.25.

[menem]

I can't find out how to get 30.95 T_T.

Regards.

[AliGW]

Nope - me neither. I wouldn't waste any more of your time until the OP has clarified and given us the information we've been asking for from the very start.

[XLHell]

I've uploaded the sheet again with menem's suggestion. I've put R4 as a formula showing the average of the last 20 cells in Row 2 (counted the cells manually). The value there is 29.083.

The initial value I mentioned was off, but the suggested formula isn't matching.

[XLHell]

I've also added a file "Sample Database Sheet A" with AliGWs formula. That is generating a #VALUE Error.

[AliGW]

The workbook is the same as before - you haven't changed anything.

How exactly have you calculated 29.083? Exactly which cells do you think should be included?

[AliGW]

You have used this:

=AVERAGE(INDEX(E2:AZZ2,LARGE(@COLUMN(E2:AZZ2)*(@E2:AZZ2<>""),20)):$AZZ2)

It should be this:

=AVERAGE(INDEX(E2:AZZ2,LARGE(COLUMN(E2:AZZ2)*(E2:AZZ2<>""),20)):$AZZ2)

[XLHell]

The last 20 cells are R2:A02. I incorrectly typed N2:AO2. But even with the correct input it's showing 30.1875. I'm updating it with the correct version. Your formula is resulting in an error value. I've uploaded that as Database Sheet A.

[AliGW]

This:

=AVERAGE(N2:AO2)

includes more than 20 non-blank cells.

Could you please explain exactly which cells you want to include and why???

[menem]

How to get 'N' value ?

Regards.

[AliGW]

Please try this:

=AVERAGE(INDEX(E2:AZZ2,LARGE(COLUMN(E2:AZZ2)*(E2:AZZ2<>""),20)-4):$AZZ2)

[XLHell]

I uploaded your formula correctly. It's now showing 25.25. But in R4 I've calculated the average of the last 20 rows (I've counted this four or five times to make sure) and it's showing 30.1875. I've calculated it manually with a calculator and the real value is 28.1, so I think Excel is mistakenly considering some added cells for some reason I don't understand.

[XLHell]

Perfect. This worked. Thank you so much.

[AliGW]

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) 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 all those who offered help.

[XLHell]

This formula works perfectly, but it's generating #VALUE errors when I drag it down. Is there a solution for this?

[XLHell]

I understood what's happened. This formula works perfectly once there are 20 values, but my database will have some entries with a lower sample size than that. Is there a solution that's more elegant than switching the value from 20 to something else.

[XLHell]

After checking again, it works perfectly in all cases. Thanks a ton, I'm updating this as solved.

[AliGW]

Remove any @ signs that appear when you open my file - that's an issue between versions of Excel.

Glad it's workking.