Excel 2003 need help with averages

[Badlizz]

Been struggling trying to come up with an average calculation that allows me to only sample the last 10 days, on a continuous basis.

I would like to add new data to my spreadsheet everyday and not have to re-write the average formula to only sample the last 10 days(or 7 days)

below is a sample of my data, in a1 I have =today() command and in a3 I have a formula that reduces date by 10 days.

any help would be appreciated. I have tried averageif averageifs - guess I just don't understand what I am doing wrong.

* edit: also if anyone can help me with my trending formula in the first row, it seems to only trend from high to low.

 

[AhoyNC]

See attached link to average last 5 rows (you should be able to adapt for last 10 rows).

Excel Magic Trick 856: Last 5 Values In Column: Formulas For Adding, Averaging and Extracting Data - YouTube

 

[Badlizz]

trying to adapt the numbers so that I can have it at the head of each column, now it creates a circular reference, and just tweaking the range from A:A to A6:A1000 isn't working, any thoughts ? the formula that he gives is
[TABLE="width: 66"]
<colgroup><col width="66"></colgroup><tbody>[TR]
[TD="class: xl63, width: 66, align: right"]=AVERAGE(INDEX(A:A,MATCH(9.99999999999999E+307,A:A)-4):INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Badlizz]

oops nevermind I think I got it working now

 

[Jonmo1]

Could you do

=AVERAGEIF(A6:A100,">="&TODAY()-9,B6:B100)


I did -9 because the last 10 days includes today..right? Just change to 10 if you like.

 

[AhoyNC]

Change the range to something like B5:B1000 (if your data will go past row 1000 just increase it).

Excel Workbook
	A	B	C
1
2	Average	426.9
3
4
5		Ember
6	22-May	400
7	23-May	450
8	24-May	375
9	25-May	375
10	26-May	400
11	27-May	350
12	28-May	749
13	29-May	425
14	30-May	390
15	31-May	375
16	1-Jun	380
17
Sheet

 

[AhoyNC]

I should have looked closer at your formula. You need to change cell reference to B:B for Ember, C:C for Ease, etc.

 

[Badlizz]

thank you for your replies, since I am only doing the average of the last 10 days it doesn't matter if the array includes the formula, I was just being dumb when I made my formula, got it fixed now, thank you again

 

[Badlizz]

yes that is what I did wrong lol, you caught me