If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
AVERAGE last 4 Mondays?
I hope I am not coming to the well too often but the help people provide
here is incredible. I have a data sheet that will not be seen by users. They input data daily using a form. Column A has the date and B and C have numbers associated with that particular date. Data is entered daily but may always not be in order. The users are not able to sort the sheet. Since data are entered daily the number of rows of data will continue to grow over time. Can someone suggest a formula that will look through all the data and show the average for the last 4 mondays? I will need to do the same for each day of the week. Thank you |
#2
|
|||
|
|||
AVERAGE last 4 Mondays?
Ted,
Not extensively test but seems ok. Dates in Col A and data in Col B. Note it ignores any date in the future and any valid date if there are no data entered in Col B. The formula as posted does the last 4 Sundays. Enter as an ARRAY and drag down 6 cells for the other 6 days of the week =AVERAGE(IF(WEEKDAY($A$1:$A$100)=ROW(A1),IF($A$1:$ A$100=TODAY(),IF($A$1:$A$100TODAY()-28,IF($B$1:$B$100"",$B$1:$B$100))))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Ted" wrote: I hope I am not coming to the well too often but the help people provide here is incredible. I have a data sheet that will not be seen by users. They input data daily using a form. Column A has the date and B and C have numbers associated with that particular date. Data is entered daily but may always not be in order. The users are not able to sort the sheet. Since data are entered daily the number of rows of data will continue to grow over time. Can someone suggest a formula that will look through all the data and show the average for the last 4 mondays? I will need to do the same for each day of the week. Thank you . |
Thread Tools | |
Display Modes | |
|
|