Complex Formula - Explanation Required

[ChrisClark]

Hi All,

I have been given a task at work and it is to explain the following forumla:

=IF($C11=$C10, "", MAX(OFFSET($E$1,MATCH($C11,$C:$C,0)-1,0,COUNTIF($C:$C,$C12))))

All that I know about this is that this formula is taking the average of several cells that have a certain textual description.

If anyone could help me to understand this more thoroughlly, I would be very grateful.

Thanks

 

[svendiamond]

OFFSET returns a range that is a certain number of rows and columns away from a cell. Let's go through them one by one.

=OFFSET(reference, rows, columns, [height], [width])
=OFFSET($E$1,MATCH($C11,$C:$C,0)-1,0,COUNTIF($C:$C,$C12))

So your offset formula is referring at E1 to begin with.

=OFFSET(reference, rows, columns, [height], [width])
=OFFSET($E$1,MATCH($C11,$C:$C,0)-1,0,COUNTIF($C:$C,$C12))

Then for the amount of rows to look down it has a MATCH function. MATCH is going to return the row in column C that matches the value in C11, minus one row. So if C4 is the first cell in column C that matches C11, your OFFSET formula is going to be looking at row 3.

=OFFSET(reference, rows, columns, [height], [width])
=OFFSET($E$1,MATCH($C11,$C:$C,0)-1,0,COUNTIF($C:$C,$C12))

Zero columns to the right.

=OFFSET(reference, rows, columns, [height], [width])
=OFFSET($E$1,MATCH($C11,$C:$C,0)-1,0,COUNTIF($C:$C,$C12))

The "height" is how many rows down should be included in the range we are going to return. If there are 5 cells in column C that equal the value in C12, then we'll go 5 rows down. That gives us this final OFFSET formula:

=OFFSET($E$1,3,0,5) 'which is basically just a range of cells, starting 3 rows down from E1, and extending 5 rows down from there.

So =MAX(OFFSET($E$1,3,0,5)) is just gonna find the MAX value of our range.