Help With Conditional Averaging Based On Column Header...

**jeffgreg** · 01-15-2014, 08:12 PM

Hello everyone,

I am having trouble figuring out how to execute the following function:

I have dates of business in my top row as headers. My first column consists of a list of items that are sold. Below each date header, we enter the quantity sold for a particular item on that date. Each row represents a different item. I would like to keep a running average of the number of each item sold based on whether it was a TU-W-TH or a FRI-SAT. The application for this would be used in a restaurant setting to keep track of the running sales mix of menu items sold during the week versus on the weekend. This average quantity would be used to determine forecasted sales for each menu item.

I have tried using the =AVERAGEIFS function but cannot figure out how to get it to look at each individual sales entry and the corresponding date for only that column. Any help? Thanks.

Jeff

**Tony Valko** · 01-15-2014, 08:56 PM

Try this...

Data Range

	A	B	C	D	E	F	G	H
1		1/1/2014	1/2/2014	1/3/2014	1/4/2014	1/5/2014	1/6/2014	1/7/2014
2	Item1	2	7			4	5	27
3	Item2		2	2	3		8
4	Item3		17		21			8
5
6	Item1	Tue	12
7		Wed
8		Thu

Note that the days of the week criteria are in a specific format: ddd.

This array formula** entered in C6:

=AVERAGE(IF(ISNUMBER(MATCH(TEXT(B1:H1,"ddd"),B6:B8,0)),IF(A2:A4=A6,IF(B2:H4<>"",B2:H4))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

I'll be offline the rest of the night watching the Pens - Caps game!