Averaging using the date instead of day of the week

[coliervile]

On worksheet 1 I have 18 columns (A thru R). Column A has dates starting in Row 3 (A3) and runs down to 1095 in ascending order. In Columns I, L, and O (starting in Row 3 too)has numbers (changes in daily fund closing prices) that I want to average. I want to to average Columns I, L, and O by the days of the week (Monday thru Friday) on worksheet 2 (the headers on worksheet 2 starting in A1 are the day of the week name) by using the dates in Column A. Is this possible to do this with a formula? Also can the formula average (starting in Row 3) down until the last filled row when new rows are added?
Thanks,

Charlie

 

[cgcamal]

Hi coliervile,

Please show sample of worksheet1 and worksheet2, and desired output. This output would be in sheet1 or sheet2? which column?

Regards

 

[pgc01]

Hi Charlie

To get the average of the values in column I on Sheet1, for the case of Monday and only for the filled rows, try in a cell in Sheet2:

=AVERAGE(IF(WEEKDAY(Sheet1!$A$3:$A$1095)=2,IF(ISNUMBER(Sheet1!I3:$I$1095),Sheet1!I3:$I$1095)))

This is an array formula, you have to confirm it with Control-Shift-Enter.

Please try it and adapt it to your layout.

 

[coliervile]

Thanks for your help thus far and here's the general Layout;
Sheet 1 (Funds)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Date	G	$ Change	% Change	F	$ Change	% Change	C	$ Change	% Change	S	$ Change	% Change	I	$ Change	% Change	Best Return	Least Return
2	15-Oct-07	$12.16	0.00	0.0000%	$11.58	0.00	0.0000%	$17.39	0.06	-0.3438%	$21.04	0.08	-0.3788%	$25.74	0.00	0.0000%	G	S
3	12-Oct-07	$12.16	0.00	0.0000%	$11.57	0.01	-0.0864%	$17.53	0.08	0.4585%	$21.25	0.13	0.6155%	$25.84	0.10	0.3885%	S	F
4	11-Oct-07	$12.16	0.00	0.0000%	$11.58	0.00	0.0000%	$17.45	0.09	-0.5131%	$21.12	0.17	-0.7985%	$25.74	0.04	0.1556%	I	S
5	10-Oct-07	$12.16	0.01	0.0823%	$11.58	0.01	0.0864%	$17.54	0.03	-0.1707%	$21.29	0.02	0.0940%	$25.70	0.03	-0.1166%	S	C
Funds

Sheet 2

Excel Workbook
	A	B	C	D
1		C Fund Avgerage	S Fund Avgerage	I Fund Avgerage
2	Monday
3	Tuesday
4	Wednesday
5	Thursday
6	Friday
7
Sheet2

 

[coliervile]

Nice spell job on Sheet 2- "average"

 

[coliervile]

If I input new daily data in a row how can the formula be mabe to change when I add the new data? Is it better to in put the new daily numbers below the last row or insert a new row at Row 3 and then sort it by ascending date? To all of the experts out there is there a better way overall to do what I'm trying to do?

Thanks to everyone,
Charlie

 

[Haseeb Avarakkan]

Hello Charlie, Try.

Firstly define a name. Press Control+F3, then

Name: Data
Refers to: =Funds!$A$1:INDEX(Funds!$A:$R,MATCH(1E+100,Funds!$A:$A),0)

Then enter in Sheet2 B2,

=LOOKUP(1E+100,CHOOSE({1,2},0,AVERAGE(IF(TEXT(INDEX(Data,,1),"dddd")=$A2,INDEX(Data,,MATCH(LEFT(B$1,1),INDEX(Data,1,),0))))))

Confirmed with Control+Shift+Enter, rather than Enter

Then copy down & across.

Does this help?

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";"> </td><td style=";">C Fund Avgerage</td><td style=";">S Fund Avgerage</td><td style=";">I Fund Avgerage</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Monday</td><td style="text-align: right;;">17.39</td><td style="text-align: right;;">21.04</td><td style="text-align: right;;">25.74</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Tuesday</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Wednesday</td><td style="text-align: right;;">17.54</td><td style="text-align: right;;">21.29</td><td style="text-align: right;;">25.7</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Thursday</td><td style="text-align: right;;">17.45</td><td style="text-align: right;;">21.12</td><td style="text-align: right;;">25.74</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Friday</td><td style="text-align: right;;">17.535</td><td style="text-align: right;;">21.27</td><td style="text-align: right;;">25.77</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet5</p><br /><br />

 

[Haseeb Avarakkan]

Hi, Charlie. Forgot to say. Now your data is dynamic. When you enter a new date in A:A this will update the Avg on Sheet2.

 

[coliervile]

Thank you Haseeb Avarakkan for the information. I did everything you said and it came out as you described. The fomula averaged different columns than the ones I wanted averaged...I need columns I, L and O. What do i need to make the change in the formula that you provided.

By the way I'm always amazed at the formulas the experts on here come up with...I couldn't have thought this one up in a million years! You folks are good!!!

Many thanks,
Charlie

 

[Haseeb Avarakkan]

B2, Copy across & down.

=LOOKUP(1E+100,CHOOSE({1,2},0,AVERAGE(IF(TEXT(INDEX(Data,,1),"dddd")=$A2,INDEX(Data,,MATCH(LEFT(B$1,1),INDEX(Data,1,),0)+1)))))

Red highlighted is the addition. Confirmed with Control+Shift+Enter