Offset and Match problem II

[Zarathrustra]

Offset and Match problem II

I am struggling to make the OffSet and Match functions that were kindly suggested by Bo__Ry a few days ago, and which initially seemed to solve my Offset problem. Certainly Bo_Ry’s solutions are more elegant than my own pure OffSet one.

Bo_Ry suggested:
Non-volatile formula at G3

=IFERROR(AVERAGE(INDEX(NormData!G$3:G$173,MATCH($A3,NormData!$A$3:$A$173,)-15):INDEX(NormData!G$3:G$173,MATCH($A3,NormData!$A$3:$A$173,)-1)),"")

or volatile formula at I3
=IFERROR(AVERAGE(OFFSET(NormData!I$2,MATCH($A3,NormData!$A$3:$A$173,)-1,,-15)),"")

In implementing each of these in all the relevant cells in my sheet CRP (see attached “Match and Offset Problem.xlsm”), the results differ from the real averages generated in the precedent sheet NormData. The volatile formula produces less errors than the non-volatile one. I cannot see any pattern between the correct and the incorrect results.

I am bewildered by what may be wrong. Perhaps I am implementing the formulae incorrectly? But most of the cells in which the formulae are used generated the correct averages, implying that the function is indeed being used correctly.

I would greatly appreciate some guidance/help.

[JohnTopley]

NormData average in G844

=AVERAGE(G685:G789)

not 15 days!

should be

=AVERAGE(G775:G789)

which agrees with the CRP OFFSET calculation

Lesson: Rarely do formula work only sometimes: errors are usually a result of mistakes by [we] users!

Learn to use the Excel diagnostics in particular "Evaluate Formula".

[Zarathrustra]

Thanks John for finding that.

I've now corrected that - and, as you probably noted, that made that particular row in CRP agree with the new and correct averaging for that date in CRP.

However, the rows for the two dates on both sides of that row 25, 10Feb22, are still showing errors. I have looked and looked at the averaging for those rows in NormData and I believe they have been generated correctly. See my updated attached "Match and Offset Problem Updated.xlsm".

[JohnTopley]

You need to re-check your calculations

in CRP g37 you compare G843 vs G24

G843 sums 705:719

compared with G24

which sums 744-758

Excel will NOT provide different results when it executes SUM/AVE the same cells.

Use Evaluate Formula to establish the OFFSET range being summed

[Zarathrustra]

Many thanks John for finding those errors - which I ought not to have done. A fresh pair of eyes helps.

I do try and be careful, but having so many figures I trip up. Sometimes I fear I try too hard.