Lookup formulas from previous weeks

[americanpie3]

Hi Everyone,

I run a dart league and we keep averages week to week and cumulative. The problem I'm having is that my sheets are not dynamic enough. Here's what I would like to do.

On sheet 1

Dan's average for Week 1 formula is =IFERROR(AVERAGE($B2:$X2),0)

On sheet 2
Dan's average for Week 2 formula is =IFERROR(AVERAGE($B16:$X16,0)

Cumulative average is

=IFERROR(AVERAGE(Week1!B2:X2,Week2!B4:X4),0)

What I want to avoid is copying for formula from the cumulative average from week 2 and create week 3 with week 3 scores.

Normally the =(cell) copies the value and not the formula. Is there something I can put in my cumulative average week cell to copy over the formulas from the previous week and then I only add to the current week's sheet?

Thanks

 

[Andrew Poulsom]

Why don't you put all your data on one worksheet with a column for week number? Then you can use AVERAGEIF or AVERAGEIFS.

 

[americanpie3]

Why don't you put all your data on one worksheet with a column for week number? Then you can use AVERAGEIF or AVERAGEIFS.

Hi Andrew,

If you look at the formula it's a lot of numbers. Plus there are total of 30 players and for 30 weeks. 1 worksheet won't do it

Thanks

 

[Andrew Poulsom]

30 times 30 is only 900. Excel has a lot more rows than that.

 

[americanpie3]

30 times 30 is only 900. Excel has a lot more rows than that.

Hi Andrew,

I don't want to waste time explaining my sheet but I will say this. Your idea makes the excel sheet very messy and hard to reference. Oh and it's actually 30x30x30 = 27000 PLUS categorizing each week AND if you've ever played darts there are more stats than just average.

Now, if you are here to argue my excel sheet rather than answer my initial question, please move on.

Thank you.

 

[Andrew Poulsom]

Moving on. I hope someone else can answer your initial question.