Average with multiple conditions but only when occur in a sequence

[Edgie]

Hi,

I would like to create a formula that would average a range with multple criteria but specifically only when conditions are in a sequence, hope that makes sense. So instead an average if over the whole range, only do it over the range where the conditions are met.

So in the Run Cost Average column i'd like to average the Color and if the status is "Run". If the next row has the same colour i'd like to include that in the average but as soon as the colour changes in the sequence or one has not run the average stops.

I have tried and failed with multiple helper cells

Hopefully that makes sense

*uploaded excel file example

Color	Status	Cost	Run Cost Average
Red	Not Run	5
Red	Run	10	7.5
Red	Run	5	7.5
Blue	Run	5	5
Red	Not Run	5
Red	Run	15	15

[Pepe Le Mokko]

The yellow banner has instructions on how to add a sheet

[oeldere]

I used 4 helpcolumns to get the result.

After that a pivot table.

See the attached file.

[Bo_Ry]

Please try at D2

=IF(B2="Run",AVERAGE(INDEX(C$2:C2,MATCH(2,INDEX(1/(1-(A$1:A2=A2)*(B$1:B2=B2)),))):INDEX(C2:C10,MATCH(1,INDEX(1-(A2:A10=A2)*(B2:B10=B2),),)-1)),"")