Moving Average of separate data sets within the same column

**excelhelpguy123** · 06-03-2017, 01:35 PM

Hello,

I am trying to have a moving average of 3 cells. To set the scene I will describe what my trouble is. I have column A with many different names but most repeat 10 times, some do not repeat. Column B has numbers. I want to take the moving average of 3 cells for person x, but when person y starts I want the moving average to stop and do moving average for person y. So, person x first 3 averages would be N/A and the fourth would average the first 3. Then when person y starts I want their first 3 to be N/A and then start at the fourth. A problem is person z has only 2 data rows so they will not have an average due to lack of data.

Thanks for the help in advance!

**Glenn Kennedy** · 06-03-2017, 01:41 PM

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**excelhelpguy123** · 06-03-2017, 01:50 PM

Attached is an example.

**kvsrinivasamurthy** · 06-04-2017, 01:00 AM

In C2, then drag down

=IF(OR(COUNTIF($A$1:A1,A1)<3,A2<>A1),NA(),AVERAGE(INDIRECT("B"&ROW()-3&":B"&ROW()-1)))

**Glenn Kennedy** · 06-04-2017, 03:10 AM

Nice one... One minor tweak to improve the appearance:

=IF(OR(COUNTIF($A$1:A1,A1)<3,A2<>A1),NA(),TEXT(AVERAGE(INDIRECT("B"&ROW()-3&":B"&ROW()-1)),"0.00"))

Moving Average of separate data sets within the same column

LinkBack

Thread Tools

Rate This Thread

Display

Moving Average of separate data sets within the same column

Re: Moving Average of separate data sets within the same column

Re: Moving Average of separate data sets within the same column

Re: Moving Average of separate data sets within the same column

Re: Moving Average of separate data sets within the same column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] calculate exponentially weighted moving average (EWMA) in one cell without separate column

How do i automatically populate Moving average charts from large data sets?

[SOLVED] Moving multiple columns (8 x sets of 3) with data in separate rows into 3 columns

Moving Column to Separate Sheet Macro

[SOLVED] Two separate charts in one (4 sets of data)

Excel 2007 : Column average according to separate column value linked to separate sheet

[SOLVED] moving average of figures from separate tables

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions