Average most recent 3 values excluding dashes - array formula help required

Howdy,

I have a range of 10 cells A1:J1 that are progressively filled with scores each week. I am trying to determine the average of the most recent 3 scores and while researching I found a formula that does almost exactly what I need. It's an array formula and they are a weakness of mine as I rarely have cause to use them. I'm having difficulty adapting this, or even understanding whether this is something that can be achieved with this formula.

{=AVERAGE(INDEX(A1:J1,LARGE(IF(A1:J1<>"",COLUMN(A1:J1)-COLUMN(A1)+1),3)):J1)}

This formula is perfect except for one thing. The values will sometimes contain a dash which just means no score. These are different from blank cells which will always be on the right side waiting to be filled whereas the dashes will be mixed in with the scores. I am trying to get the average of the most recent 3 scores while ignoring the dashes. So if the first 6 values [edit - the only 6 values, which would be stored in A1:F1] are 20 30 40 - - 50, the average would be (30,40,50) = 40, not (-,-,50) = 50. As far as I can tell, getting this formula to reference B1:J1 (30 40 - - 50 [and four blanks]) in the example would be OK but at the moment it references D1:J1.

I have considered trying to add a COUNTIF for the dashes in the resulting range and using that to make an adjustment but if there are several dashes in a row I can see the formula getting out of hand. I'm sure there is a more elegant way to do it but I haven't even figured out the logic yet let alone a way to implement it. I'm keen to learn about arrays but this is definitely beyond me at the moment.

Any help or suggestions would be appreciated!

Try:

Please Login or Register  to view this content.

Ctrl-shift-enter

Originally Posted by cricket_stoner

{=AVERAGE(INDEX(A1:J1,LARGE(IF(A1:J1<>"",COLUMN(A1:J1)-COLUMN(A1)+1),3)):J1)}

Assuming there will always be 3 numbers to average...

Replace: A1:J1<>""

With: ISNUMBER(A1:J1)

Still array entered.

Hi


=SUM(INDEX(A1:J1,AGGREGATE(14,6,COLUMN(A:J)/ISNUMBER(A1:J1),INDEX(ROW(1:3),0))))/3

I've to adjust the denominator.

Just for sharing some idea.

Hi All


a little fix, if count of numbers could be lesser than 3

=IFERROR(SUM(INDEX(A1:J1,AGGREGATE(14,6,COLUMN(A:J)/ISNUMBER(A1:J1),INDEX(ROW($1:$3),0))))/3,AVERAGE(A1:J1))

Regards

You're welcome. We appreciate the feedback!