Hello,

I would like to calculate the last three months based on available data set, my data set are below;

Product	 Date	       Price
A	11/28/2014	114.5
	09/11/2014	114.5
	10/22/2012	58.21
	11/14/2011	71.91
	12/08/2010	71.91
	01/13/2010	74.38
B	12/23/2014	14.51
	01/24/2014	14.89
C	09/23/2014	84.17
	04/29/2014	84.17
	08/14/2012	77.85
	05/15/2007	60.5
	06/25/2005	76.73
	05/25/2004	0

So in this example i want to calculate the last three month average price per product, but the problem for me is sometimes i have only two dates available like example B that time i want to average based on two dates only.

Kindly help me in finding out the average for the data set.

Hi,

This forum is for developers discussing developing issues involve Excel application like Excel customization and autoamtion.

Since the issue is more relative to end-user, I would like to move it to Excel IT Pro Discussions forum.

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

Thanks for your understanding.

Regards & Fei

So in this example i want to calculate the last three month average price per product

The average over the last 3 month is #NA, because the dates are all from the last year. ;-)

I guess you want the average of the last 3 prices of a product?! For that you can use MATCH to get the first row number, then use OFFSET to include the next 2 rows and count the number of blank cells in that range.

Use OFFSET in column C with the result + the 1st row and calculate the average.

Use the values, resp. formulas in the mentioned cells as follows:

E2:  Product
E3:  Row
E4:  Count
E5:  Average
F2:  B
F3:  =MATCH(F2,A:A,0)
F4:  =COUNTBLANK(OFFSET(A1,F3-1,,3))
F5:  =AVERAGE(OFFSET(C1,F3-1,,1+F4))

Andreas.

Hi Andreas, thanks for the reply.  Can you please provide me this on sample excel file based on my example because it's hard to understand for me based on your example... .

Copy your sample data into a new file, so that "Product" is in A1, that is what I've done.

Andreas.

Yeah i got it now.. thanks for it. but for me the same way i have around 2000 products are there so i cannot able to each into the same way.. is there any way to get it in a separate column as average results.

Move the formulas from column F to row 2, create a list of all products in column F and drag the formulas down.

F2:  A
G2:  =MATCH(F2,A:A,0)
H2:  =COUNTBLANK(OFFSET($A$1,G2-1,,3))
I2:  =AVERAGE(OFFSET($C$1,G2-1,,1+H2))

Hint: Filter the column of the products and hide empty cells, then copy the column into a new sheet. Excel copies the visible cells only.

Andreas.

Move the formulas from column F to row 2, create a list of all products in column F and drag the formulas down.

F2:  A
G2:  =MATCH(F2,A:A,0)
H2:  =COUNTBLANK(OFFSET($A$1,G2-1,,3))
I2:  =AVERAGE(OFFSET($C$1,G2-1,,1+H2))

Hint: Filter the column of the products and hide empty cells, then copy the column into a new sheet. Excel copies the visible cells only.

Andreas.

• Marked as answer by vinwin1985 Wednesday, June 24, 2015 7:36 AM
• Unmarked as answer by vinwin1985 17 hours 48 minutes ago
• Marked as answer by vinwin1985 16 hours 0 minutes ago

Hello Andreas,

There is one issue on the formula you have provided, like incase if product has only one rate then its has to pick that one , but as per the formula its taking average of the same like dividing by two and only half of the price is coming as my answer. can you please help to fix it.

There is one issue on the formula you have provided, like incase if product has only one rate then its has to pick that one

In that case the following cell below the product is not empty:

I2: =AVERAGE(OFFSET($C$1,G2-1,,1+IF(ISBLANK(OFFSET($A$1,G2,0)),H2,0)))

Andreas.

yeah its works now. thank you so much.