# Average between next occurence of values in a column



## sonflower (Dec 21, 2022)

Hi, 

I would like to find the average between the next occurence of an B (as highlighted below). Have tried to search the internet on how to group by next occurences, but could not exactly find a solution. Would appreciate any help or suggestions, thank you


----------



## Flashbond (Dec 22, 2022)

Offset and Indirect... Two sins together 

```
=IF(B2="B",AVERAGE(OFFSET(A2,,,IFERROR(MATCH("B",B3:B$16,0),COUNTA(B2:B$16)))),INDIRECT("C"&ROW()-1))
```


----------



## sonflower (Dec 22, 2022)

Flashbond said:


> Offset and Indirect... Two sins together
> 
> ```
> =IF(B2="B",AVERAGE(OFFSET(A2,,,IFERROR(MATCH("B",B3:B$16,0),COUNTA(B2:B$16)))),INDIRECT("C"&ROW()-1))
> ```


Thank you so much! It works perfectly. I'm struggling to understand the formula however (If error, countA and indirect), would you mind explaining? Thanks in advance!


----------



## Flashbond (Dec 22, 2022)

The formula depends on the finding next "B" value with MATCH function to determine OFFSET height.

On the very last set, MATCH function won't be able to find the next "B". So, if MATCH function returns an error, then COUNT of remaining cells will be the new offset height at the very end. A better idea can be "check if next cell is empty, then do something". But I couldn't come up with a solution on that direction.

INDIRECT is unnecessary when I think again since you have table headers. You can move on like this:

```
=IF(B2="B",AVERAGE(OFFSET(A2,,,IFERROR(MATCH("B",B3:B$16,0),COUNTA(B2:B$16)))),C1)
```


----------



## shinigamilight (Dec 22, 2022)

C2:                =IF(B2="B",AVERAGE(OFFSET(A2,0,0,MATCH("B",B3:$B$17,0),1)),C1)


----------



## Fluff (Dec 22, 2022)

A non volatile approach.
	
	
	
	
	
	



```
=IF(B2="B",AVERAGE(A2:INDEX(A3:A1000,MATCH("B",B3:B1000,0)-1)),C1)
```


----------



## Flashbond (Dec 22, 2022)

Fluff said:


> A non volatile approach.
> 
> 
> 
> ...


I tried also this method. But it gives a false average of all rows if there are consecutive "B"s since the row index would be 0.


----------



## Fluff (Dec 22, 2022)

Good point, this will correct that
	
	
	
	
	
	



```
=IF(AND(B2="B",B3<>"B"),AVERAGE(A2:INDEX(A3:A1000,MATCH("B",B3:B1000,0)-1)),IF(AND(B2="B",B3="B"),A2,C1))
```


----------



## sonflower (Dec 29, 2022)

Thank you all for your help, happy new year!


----------



## Fluff (Dec 29, 2022)

Glad we could help & thanks for the feedback.


----------

