Offset Error

[USS_Ominsky]

This problem has been plaguing my team for months and its about time I asked the pros.

Within a larger formula, I have an OFFSET term within a larger "IF" construct that evaluates to two different answers:

OFFSET('OTHER_Sheet'!$D53,-MOD(MROUND(YEARFRAC($F60,J$4,0),1/12)*12,M$6+M$7)+M$7,J$2)​

Both when this calculates and when I step into the function with alt+M+V, the term above simplifies to

OFFSET('OTHER_Sheet'!$D53,-4,2) = 210,956 which is the value of cell 'Other_Sheet'F50​

However, when I select each individual term in the formula bar and solve step by step with F9, the term above also simplifies to

OFFSET('OTHER_Sheet'!$D53,-4,2) = 211,752 which is the value of cell 'Other_Sheet'F49 and is the "correct" reference​

As I have this formula pasted down several rows, I can see that this error repeats itself in irregular intervals. It seems to me that the actual error must be in the MROUND to the nearest twelth and subsequent multiplication, but there is no reason we can think of why this OFFSET would evaluate the same numbers 2 different ways.

 

[Marcelo Branco]

Welcome to Mr Excel

It seems a rounding issue - not sure.
Maybe the 4 that you see, when you step into the function, be, in fact, something like 3.999... (not exactly 4) and OFFSET takes in account only the Integer portion: 3

See if this fix it
OFFSET('OTHER_Sheet'!$D53,ROUND(-MOD(MROUND(YEARFRAC($F60,J$4,0),1/12)*12,M$6+M$7)+M$7,0),J$2)

Hope this helps

M.