Require a formula to average a certain number of cells dependent on another cell

Hi everyone, I am after a way to average a certain number of cells dependent on a number in another cell.

Basically I have five years of weekly data and I want to be able to average the year to date for each year. In other words, in the current year I have a count formula in a cell to count how many weeks have data in it. I then average the year to date data by the number in this cell (the number of weeks that have data in them).

I want to be able to average the previous years data up until the current week as well so that we can see where we are sitting compared to previous years at the same point.

Say if we are up to week 20, I only want to take the data from weeks 1 - 20 of the previous years and average by 20 weeks.

I am not sure if this possible but I wanted to put the idea out there and see if anyone can help.

I would really appreciate any advice.

Thanks, Rosie

You don't tell us which columns you are using for your dates or for your values, so I can't give you a specific formula. Perhaps you can add a sample workbook - the FAQ describes how to.

Hope this helps.

Pete

Hi Pete, Thanks for such a quick reply. I will add more info now.

Hi, I hope this works. I have added a snapshot of my workbook.

Cell I48 has this formula in it =COUNTIF(H4:H44,">0")which counts the number of weeks utilised to date

Cell H48 has the formula =SUM(H46/I48) which gives me the average of the total to date by the weeks utilised to date

I am trying to get an average of columns C - G but only by the number of weeks to date. For example the average of C4:C26 / I48

Thanks

A picture is no good to me - I can't play about with various formulae, and I'm certainly not going to try to recreate the file from scratch. Please attach an Excel workbook instead.

Pete

Sorry Pete, Ill try again.

Sample workbook for excel forum.xlsx

I am not sure if this has uploaded correctly. I will click submit and then check it.

Ok that looks as though it worked. I just copied the part of the workbook I am referring to into a new workbook.

Your COUNTIF formula is actually in cell H49. You can put this formula in B49:

=AVERAGE(B$5:INDEX(B$5:B$45,$H49))

then copy across to G49. You can check that it is picking up the correct range by putting this formula in I26 (say):

=AVERAGE(B5:B26)

and then copying that across to give you the averages to date directly (but it is not dynamic, so remove it afterwards).

Hope this helps.

Pete

Pete you are the best! That worked.

Sorry, when I copied into a new workbook the data moved left and up one cell so it was off by one cloumn and row so I adjsuted the formula back for my workbook and it works perfectly.

Thanks so much for getting back to me so quickly. I can update all of my spreadsheets with this formula now.

Thanks again

You're welcome - glad to be of help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile), and you can add your own private comment.

Pete

Great, Thanks Pete. I have done both

Okay, thanks for that.

If you need to post again remember that attaching a sample Excel workbook will enable your query to dealt with more quickly.

Pete