In excel, I need to calculate average in column B for every few data thats in Column A but need to skip total. But column A doesnt have a consistent sequence. Is it possible to do sth like the picture? I have 100k records so doing it manually is not very efficient. Please advise if there a way doing it, thanks

The logic is that First Average number is based on B2:B6, second average is B8:B11 that is skipping all the "total"

CodePudding user response：

Use INDEX/MATCH

=IF(OR(A1="TOTAL",ROW()=2),AVERAGE(B2:INDEX(B2:B$1040000,MATCH("TOTAL",A2:A$1040000,0)-1)),"")

Put that in C2 and copy down the dataset.