avergeif function

[rickblunt]

Hello, I am using the following formula to average negative values in a column, and I would like to tweak it to only average the last "X" number of cells. Hmmm... I may even just assign another cell for this "X" value so that the user can change the number. Anyway, I am not sure how to tweak the formula for that, should I use an OFFSET, or maybe the COUNT? I tried a couple of things but it was not even close. I appreciate any suggestions, thank you.

=AVERAGEIF(C:C,"<0")

 

[jasonb75]

Do you want the last X number of total cells or the last X number of negative cells?

Which version of excel are you using?

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[rickblunt]

Do you want the last X number of total cells or the last X number of negative cells?

Which version of excel are you using?

Thanks for the help jasonb75, It is Windows and Office 2019 - I have updated my profile.

To answer your question it would be the last X number of negative cells. This is basically a log of tank levels and I am trying to figure out how much we use per day/week - but I need to account for the "fills" when they refill the tank.

 

[rickblunt]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks for reminder Joe4, I should have done that a long time ago.

 

[AhoyNC]

Try: Not sure, but you may need to enter this as an array formula in Excel 2019 with CTRL-SHIFT-ENTER.

Book1
	C	D	E
1	Data		Number of <0 values to Average
2	-12		5
3	-3		Average
4	-14		-8
5	-18
6	30
7	-6
8	-18
9	-12
10	-3
11	4
12	-5
13	7
14	-2
15
Sheet1

Cell Formulas
Range	Formula
E4	E4	=IF(COUNTIF(C:C,"<0")<$E$2,"Too few values",AVERAGEIF(INDEX(C:C,LARGE(IF(C:C<0,ROW(C:C),""),$E$2)):INDEX(C:C,MATCH(2,1/(C:C<0))),"<0"))

 

[rickblunt]

Try: Not sure, but you may need to enter this as an array formula in Excel 2019 with CTRL-SHIFT-ENTER.

Book1
	C	D	E
1	Data		Number of <0 values to Average
2	-12		5
3	-3		Average
4	-14		-8
5	-18
6	30
7	-6
8	-18
9	-12
10	-3
11	4
12	-5
13	7
14	-2
15
Sheet1

Cell Formulas
Range	Formula
E4	E4	=IF(COUNTIF(C:C,"<0")<$E$2,"Too few values",AVERAGEIF(INDEX(C:C,LARGE(IF(C:C<0,ROW(C:C),""),$E$2)):INDEX(C:C,MATCH(2,1/(C:C<0))),"<0"))

Geez, I wasn't even close! lol. So I needed to use the index and match functions, that's cool. It worked perfectly and yes, I had to enter it as an array. Thank you for the help and the knowledge - it is very appreciated.

 

[jasonb75]

So I needed to use the index and match functions, that's cool.

There are many ways, it would work with offset or index but given that offset is volatile many people opt for alternatives where possible.
That said, there is no right or wrong way, although some people will often have you thinking that there is (yep, I'm guilty of it).
Formulas can also be made to work more effectively by making ranges dynamic, splitting complex calculations over several cells, or even a combination of both as shown below.
The arrays used in this method are much smaller than those that @AhoyNC has used, although both will include all data in the column mine excludes anything below the last row of data (over 1 million extra cells that don't need to be calculated).

Book1.xlsx
	C	D	E	F	G	H
1	Data		Number of <0 values to Average
2	-12		5		last row	14
3	-3		Average		Row of first -ve to include	8
4	-14		-8
5	-18
6	30
7	-6
8	-18
9	-12
10	-3
11	4
12	-5
13	7
14	-2
Sheet2

Cell Formulas
Range	Formula
H2	H2	=MATCH(1E+100,C:C)
H3	H3	=AGGREGATE(14,6,ROW(C2:INDEX(C:C,H2))/(C2:INDEX(C:C,H2)<0),E2)
E4	E4	=AVERAGEIF(INDEX(C:C,H3):INDEX(C:C,H2),"<0")

 

[rickblunt]

There are many ways, it would work with offset or index but given that offset is volatile many people opt for alternatives where possible.
That said, there is no right or wrong way, although some people will often have you thinking that there is (yep, I'm guilty of it).
Formulas can also be made to work more effectively by making ranges dynamic, splitting complex calculations over several cells, or even a combination of both as shown below.
The arrays used in this method are much smaller than those that @AhoyNC has used, although both will include all data in the column mine excludes anything below the last row of data (over 1 million extra cells that don't need to be calculated).

Book1.xlsx
	C	D	E	F	G	H
1	Data		Number of <0 values to Average
2	-12		5		last row	14
3	-3		Average		Row of first -ve to include	8
4	-14		-8
5	-18
6	30
7	-6
8	-18
9	-12
10	-3
11	4
12	-5
13	7
14	-2
Sheet2

Cell Formulas
Range	Formula
H2	H2	=MATCH(1E+100,C:C)
H3	H3	=AGGREGATE(14,6,ROW(C2:INDEX(C:C,H2))/(C2:INDEX(C:C,H2)<0),E2)
E4	E4	=AVERAGEIF(INDEX(C:C,H3):INDEX(C:C,H2),"<0")

Thanks for the additional input, it is valued and I will try it out as well. I am always amazed on how you are able to build a formula that is so complex, and sometimes how it turns out to not be so complex as well. I can usually understand how it works as I study it out, but I am in awe of how they get put together.

I have quite a collection of "good" solutions from you gurus in my little black book, and it is indispensable to me

 

[AhoyNC]

Glad we could help. Thanks for the feedback.