Averaging Values

[weefatb0b]

Hi
Sorry I cannot upload a sample spreadsheet, so I am hoping this make sense?

I have been tasked with averaging payments over the last 52 out of 104 weeks all of my value cells have been formatted to number with 2 decimal places

I have tried
IFERROR(AVERAGEIF(B3:DA3,"<>0"),"") and get the average per week for the 104 weeks ignoring any 0.00 values. Which calculates correctly for any that have under 52 weeks of values greater than 0.00

But if the count of the weeks are greater than 52, which I have counted in a separate cell (DC3), I need to average the first 52 weeks, that have a value greater than 0.00, which may not necessarily be week 1 to 52

For this I have used
IFERROR(AVERAGEIF(DC3,">=52",B3:BA3),DB3)
Which should be if dc3 is greater than 52, average the first 52 cells, B3:BA3 else return the value in DB3 which is a higher total than the 1st formula.

I have then tried
IFERROR(AVERAGEA(B3:BA3,DC3>=$DJ$5),DB3)
Which gives a higher total than formula 1 but a lower total than formula 2

And if I use
AVERAGE(B3:BA3)
I get a different total again which matches the first 52 weeks in my lookup table.

I guess what I am asking is which one is correct, if any or is it actually possible to do what I am being asked?

Thanks in advance

 

[weefatbob]

Week 1 would be 1
Week 3 would be 2
Week 4 would be 3
Week 5 would be 4
Week 7 would be 5?
Yes, for this project we need 52 weeks of <> 0 and can go back 104 weeks to get this,

 

[weefatbob]

Does this help? Average is giving a different total to the AverageIf formula?

week 1	14​
week 2	0​
week 3	6​
week 4	6​
week 5	0​
week 6	4​
week 7	10​
week 8	14​
week 9	8​
week 10	0​
week 11	6​
week 12	12​
week 13	0​
week 14	14​
week 15	10​
Average of 15 weeks	6.933333​	AVERAGE(B1:B15)
Average of 1st 5 values greater than 0	5.714286​	AVERAGE(B1:B7)
Average of 1st 5 values greater than 0 using formula	10​	AVERAGEIF(B1:INDIRECT(ADDRESS(3,COLUMN(B1)+5)),"<>0",B1:INDIRECT(ADDRESS(3,COLUMN(B1)+5)))

 

[AhoyNC]

Sorry, but I haven't been able to come up with a formula that will work with all your different scenarios.

 

[weefatb0b]

Sorry, but I haven't been able to come up with a formula that will work with all your different scenarios.

Thanks anyway, your help has got me more advanced than I was, so been helpful