CODEWITHSUNDEEP
excelexcel-formula
Angelika
Angelika

Reputation: 217

Excel: averageif function for more than one cell

I have data like this:

   A  B  C  D  E
1  6  7  8  9  1
2  1  2  3  4  5
3  N2 N4 N3 N5 N1

All the values are associated with numbers (N1, N2 ...). I know how to get one value by number using SUMIF:

=SUMIF($A$3:$E$3; "N1"; $A$2:$E$2) ' will get "5"

But I have another task: I need to get average of last m values before value founded with SUMIF (or AVERAGEIF).
For example for m=3, N = N1 and row = 2: we'll find number "5" and get average of last three values in row before N1: (5 + 4 + 3) / 3 = 4
How could I do it correctly?

Upvotes: 0

Views: 102

Answers (3)

basic
basic

Reputation: 11968

Another option:

=SUMPRODUCT(A1:E2*(ROW(A1:E2)=H3)*(COLUMN(A1:E2)<=MATCH(H2,A3:E3,0))*(COLUMN(A1:E2)>(MATCH(H2,A3:E3,0)-H1)))/H1

enter image description here

Upvotes: 2

AnilGoyal
AnilGoyal

Reputation: 26238

I propose alternate solution (supposing that first column is A)

dataset

enter image description here

Function used for result

=AVERAGE(INDIRECT(CHAR(MATCH($H$2,$A$4:$E$4,0)+65-$H$3)&ROW()&":"&CHAR(MATCH($H$2,$A$4:$E$4,0)+64)&ROW()))

Check this for diff N and m

enter image description here

OR

enter image description here

If your data doesn't start with A always you have to tweak the values for CHAR() i.e. 64 and 64+1=65 only.

Upvotes: 1

JvdV
JvdV

Reputation: 75990

In case you do not have Excel O365, you could use:

enter image description here

Formula in L2:

=SUMPRODUCT(INDEX(A1:E2,I3,ROW(INDEX(A:A,MAX(MATCH(I2,A3:E3,0)-I1+1,1)):INDEX(A:A,MATCH(I2,A3:E3,0)))))/I1

A bit lenghty, but it will also work if you use N4 or N2 while you can have "m=3".

If one has O365, you can use:

=SUM(INDEX(A1:E2,I3,SEQUENCE(I1,,MATCH(I2,A3:E3,0)-I1+1)))/I1

That's made on the assumption you will always have "m" values available.

Upvotes: 2

Related Questions