#### BlackMagic

##### New Member
Hello I am trying to average a list of Pricing of a particular product and a specified date. I know how to get the dates but I without each cell referencing the product (just the top row) I can't figure out how to pull the averages.
 Blue Shirt Red Shirt Grey Shirt Black Shirt Date LCBR LCBS LCBT LCBU 9/1/2014 200 260 250 360 9/8/2014 250 245 260 355 9/15/2014 225 230 255 350 9/22/2014 230 250 300 330

<tbody>
</tbody>
Taking this I want to average off of the Product code EX: LCBR and then the date range I'm seeking as well. This data goes back about 10 years I only put a portion of it here to give an example. Please help!

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Let's assume that A1:E6 contains the table, let G2 contain the start date, H2 contain the end date, and I2 contain the product code, such as LCBR. Then try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(IF(\$A\$3:\$A\$6>=G2,IF(\$A\$3:\$A\$6<=H2,INDEX(\$B\$3:\$E\$6,0,MATCH(I2,\$B\$2:\$E\$2,0)))))

Hope this helps!

According Domenic's setup, if you are using Excel 2007 or a more recent version you can also try

=AVERAGEIFS(INDEX(\$B\$3:\$E\$6,0,MATCH(I2,\$B\$2:\$E\$2,0)),\$A\$3:\$A\$6,">="&G2,\$A\$3:\$A\$6,"<="&H2)

M.

According Domenic's setup, if you are using Excel 2007 or a more recent version you can also try

=AVERAGEIFS(INDEX(\$B\$3:\$E\$6,0,MATCH(I2,\$B\$2:\$E\$2,0)),\$A\$3:\$A\$6,">="&G2,\$A\$3:\$A\$6,"<="&H2)

M.
SCORE! That rocks!!! So basically we indexed the product code matching it with the exact location and then averaged it if it's in between those dates! Life saver my friend!

SCORE! That rocks!!! So basically we indexed the product code matching it with the exact location and then averaged it if it's in between those dates! Life saver my friend!

Thanks for the feedback. Glad for helping.

M.

Replies
1
Views
210
Replies
3
Views
456
Replies
4
Views
175
Replies
6
Views
170
Replies
7
Views
194

1,219,906
Messages
6,150,908
Members
450,991
Latest member
ExcelDoer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back