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

 

[RobP]

Hi, welcome to the forum.

Looking at your profile, you mention Excel 2013 - and I think, as there is no reply yet, it might not be possible with that version.

If I understood your problem, It is to find the average of the last 52 values >0 within your row of 104 payments. The data could contain zeros, which you want to remove.
Your data (104 columns) run between B3:DA3 ?

I believe I managed to solve it with the latest version (O365) as it has a few new tricks up its sleeve.

=AVERAGE(INDEX(FILTER(B3:DA3,B3:DA3>0),SEQUENCE(1,52,COUNT(FILTER(B3:DA3,B3:DA3>0)),-1)))

This essentially looks at the last 52 columns that contain values > 0, and provides an average. If you change the "52" in the formula to "10", you can get an average over the last 10weeks etc.

There might be someone clever here who can find another solution for your particular version.. fingers crossed.. or you could persuade your organisation to upgrade maybe ?

cheers
Rob

sequence_average_payments_52weeks.xlsx
	CU	CV	CW	CX	CY	CZ	DA	DB
1
2
3	23.56	45.32	45.32	0	45.32	45.32	22.23
4
5
6						Average	40.702
7						(last 5 payments)
8
Sheet1

Cell Formulas
Range	Formula
DA6	DA6	=AVERAGE(INDEX(FILTER(B3:DA3,B3:DA3>0),SEQUENCE(1,5,COUNT(FILTER(B3:DA3,B3:DA3>0)),-1)))

 

[AhoyNC]

Maybe something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3		0	1	2	3	4	5	6	7	0	9	10	11	12	13	14	15
4
5			8	Average of first 51 numbers excluding 0
Sheet1

Cell Formulas
Range	Formula
C5	C5	=AVERAGEIF(B3:INDIRECT(ADDRESS(3,COLUMN(B3)+51)),"<>0",B3:INDIRECT(ADDRESS(3,COLUMN(B3)+51)))

 

[weefatb0b]

Maybe something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3		0	1	2	3	4	5	6	7	0	9	10	11	12	13	14	15
4
5			8	Average of first 51 numbers excluding 0
Sheet1

Cell Formulas
Range	Formula
C5	C5	=AVERAGEIF(B3:INDIRECT(ADDRESS(3,COLUMN(B3)+51)),"<>0",B3:INDIRECT(ADDRESS(3,COLUMN(B3)+51)))

Thanks for your replies, was on a day off yesterday, so apologies for my late response.
AhoyNC this is as near as been able to get it, thanks, |It is kind of doing what I need, but its the 1st 52 cells after B3 that do not have a zero balance, which could go up to cell 56 or so, I need to average. I dont even know if what I am looking for is possible.

 

[RobP]

Hi, I'm not sure if I understood correctly - as I thought you wanted the last 52 payments out of your 104 listed. (ie. B3 to DA3).

but above you mention now you want the first 52 payments (which I believe is what @AhoyNC has done. If you want it to count backwards, from DA3 (ie. as if you had 104 weeks, but wanted only last 52 within that which are not zero, you can just change it to :

=AVERAGEIF(DA3:INDIRECT(ADDRESS(3,COLUMN(DA3)-52)),"<>0",DA3:INDIRECT(ADDRESS(3,COLUMN(DA33)-52)))

When I compare the value below to my original formula, its the same .. Does this help ?

sequence_average_payments_52weeks.xlsx
	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD
1
2
3	45.32	45.32	45.32	45.32	23.56	45.32	45.32	0	45.32	45.32	22.23
4
5
6										Average	40.78154	(my original formula)
7										(last 52 payments excl. zeros)
8										Average	40.7815	(Ahoy formula reversed)
9
10
11
Sheet1

Cell Formulas
Range	Formula
DA6	DA6	=AVERAGE(INDEX(FILTER(B3:DA3,B3:DA3>0),SEQUENCE(1,52,COUNT(FILTER(B3:DA3,B3:DA3>0)),-1)))
DA8	DA8	=AVERAGEIF(DA3:INDIRECT(ADDRESS(3,COLUMN(DA3)-52)),"<>0",DA3:INDIRECT(ADDRESS(3,COLUMN(DA33)-52)))

 

[Fluff]

When I compare the value below to my original formula

But the OP is using xl2013 & so doesn't have functions such as Filter & Sequence.

 

[RobP]

I know.. thats why we use Ahoys formula.

 

[AhoyNC]

So it's the first 52 cells after B3 without a zero?
Then try:
AVERAGEIF(B4:INDIRECT(ADDRESS(3,COLUMN(B4)+51)),"<>0",B4:INDIRECT(ADDRESS(3,COLUMN(B4)+51)))

 

[weefatbob]

Hi Guys, sorry its still not working for me, see below

Actual Total OF 104 WEEKS	52 week total Using formula	Average 52/104 week pay	Manual Calculations
		52​
	DK	DL
7653.20​	28,690.24​	551.74​	130.54​	Difference	########​
SUM(H6:DG6)	AVERAGEIF(H6:INDIRECT(ADDRESS(3,ROW(H6)+51)),"<>0",H6:INDIRECT(ADDRESS(3,ROW(H6)+51)))	DK6/DL4
should be:	6788.30​	130.54​
	SUM(H7:CZ7)	DK8/DL4
19202.80​	14,654.21​	281.81​	247.78​	Difference	-34.03​
SUM(H10:DG10)	AVERAGEIF(H10:INDIRECT(ADDRESS(3,ROW(H10)+51)),"<>0",H10:INDIRECT(ADDRESS(3,ROW(H10)+51)))	DK10/DL4
should be:	12884.65​	247.78​
	SUM(H11:BK11)	DK12/DL4

I have tried both ROW and COLUMN in the formula and it keeps giving me incorrect totals. H7 and H11 are mirrors of H6 and H10, that I am using as a check to make sure my formulas in H6 and H10 are returning the correct values. You can see the values under the Manual Calculations column return the same values as the Should be but I would have hoped the 52 week total using formula would match too? The should be total, 6788.30 and 12884.65 are the total for the 52 weeks from H6 and H10 respectively, any advice?

 

[AhoyNC]

I'm still confused. Look at the sample below of 15 weeks. Instead of 52 weeks, lets say you want to average 5 weeks that don't equal 0. Which of the weeks would you pick?
Also, are the max number of columns in your data going to 104?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15
2	14	0	6	6	0	4	10	14	8	0	6	12	0	14	10
Sheet2