A formula to AVERAGE IF but only average a set number of values

  • Thread starter Thread starter DonFlak
  • Start date Start date
D

DonFlak

I have a chart that has a row of data at the top.

One cell, let's say O5 has a value, let's say 5.

I need to search the O column for the value (5).

When it locates the 1st line with a 5 in column O (let's say O18) I
need it to take the value from Column N (N18) and place it in cell N5.

I then need to take the first 4 lines with the value of 5 in Column O
(the previous O18, and let's say O26, O38, and O57) and average the 4
values in column N (N18, N26, N38, & N57) then place the average value
in cell N4.

I want to search the column O beginning with line 8 and below but as I
said I only want the 1st match and the 1st 4 matches.

I can't find a way to do this but as the value in O5 continually
changes, I need to automate this process.

Any assistance is greatly appreciated.

Don
 
I then need to take the first 4 lines with the value of 5 in Column O

Will there *always* be 4 instances of 5 in column O?
 
(e-mail address removed) wrote...
....
When it locates the 1st line with a 5 in column O (let's say O18) I
need it to take the value from Column N (N18) and place it in cell
N5.
Click to expand...

Use the following formula in N5.

=IF(COUNT(MATCH(O5,O8:O1000,0)),INDEX(N8:N1000,MATCH(O5,O8:O1000,0)),
"no match")
I then need to take the first 4 lines with the value of 5 in Column
O (the previous O18, and let's say O26, O38, and O57) and average
the 4 values in column N (N18, N26, N38, & N57) then place the
average value in cell N4.
Click to expand...
....

Try the following ARRAY formula in N4.

=IF(COUNTIF(O8:O1000,O5)>=4,AVERAGE(IF((O8:O1000=O5)
*(ROW(N8:N1000)<=SMALL(IF(O8:O1000=O5,ROW(N8:N1000)),4)),N8:N1000)),
"fewer than 4 matches")

If you want to average all values if there are fewer than 4, try the
following ARRAY formula.

=IF(COUNT(N5),AVERAGE(IF((O8:O1000=O5)*(ROW(N8:N1000)
<=SMALL(IF(O8:O1000=O5,ROW(N8:N1000)),MIN(COUNTIF(O8:O1000,O5),4))),
N8:N1000)),N5)
 
Back
Top