Formula for weekly average & changing daily formula

[sandbach]

Hello

I am trying to use a formula for devising averages but having a few problems, so was wondering if anybody could help please.

I want to make a weekly average. So i want to use data from the past week, is there a way for excel to only extract data from data linked to the current date so only data from that week is used? ie if i post a total on the 5th november it will only use data from that week 1st -5th novemeber?

Next question is i have a formula

(SUM($B$4:$B$500))/(COUNTIF($B$4:$B$500,">0")) 

say on the 1st november this will do the average of all the days before the 1st november, but if i were to make a new entry for the 2nd november, it changes the 1st novembers average, i dont want it to include any entries afterwards, is this possible?

Any help really would be appreciated.



Kind regards

Joe

[Palmetto]

You might opt for using DAVERAGE, which allows the use of criteria.

Assuming your data are in the range A4:B500, where column-A values are dates and column-B values are numbers:

Using cell F4 as a date input cell for the starting date, then . . .

Set up criteria cells in, say F1:G2

F1 and G1 contains the text: Date
F2 contains: =">="&F4
G2 contains: ="<="&F4+6

formula cell for average contains: =DAVERAGE($A$4:$B$500,2,$F$1:$G$2)

[scottylad2]

Hello

I am trying to use a formula for devising averages but having a few problems, so was wondering if anybody could help please.

I want to make a weekly average. So i want to use data from the past week, is there a way for excel to only extract data from data linked to the current date so only data from that week is used? ie if i post a total on the 5th november it will only use data from that week 1st -5th novemeber?

Next question is i have a formula

(SUM($B$4:$B$500))/(COUNTIF($B$4:$B$500,">0")) 

say on the 1st november this will do the average of all the days before the 1st november, but if i were to make a new entry for the 2nd november, it changes the 1st novembers average, i dont want it to include any entries afterwards, is this possible?

Any help really would be appreciated.



Kind regards

Joe

Could you upload a small sample of what you have? doesn't sound too difficult, but much easier knowing your layout, especiall the cells to average compared to the dates