Why is my OFFSET function not working?

[Michelle]

Hello, - Sorry, I have posted this before, but it never reaches the group, I
don't know why - I have a formula that looks at some parameters, and then
returns a
value from one month or two months previously, it works most of the time,
but occasionally just doesn't. I've followed it through and used watches and
everything I can think of, but can't work out why.

the formula looks like this:
=IF(OR(MONTH(AF$7)=properties!$B$16+properties!$B$15,MONTH(AF$7)=properties!$B$16+properties!$B$15+3,MONTH(AF$7)=properties!$B$16+properties!$B$15+6,MONTH(AF$7)=properties!$B$16+properties!$B$15+9),IF(OFFSET(AF$1042,0,-properties!$B$16)<0,-OFFSET(AF$1042,0,-properties!$B$16),0),0)

But the first IF clearly equates to false, so it's really only using
IF(OFFSET(AF$1042,0,-properties!$B$16)<0,-OFFSET(AF$1042,0,-properties!$B$16),0)

the formula box displays 'V.o.l.a.t.i.l.e' (without the dots) as the result
of the formula but the values are as follows:
AD1042 = -60 (this is the cell 2 to the left of AF1042 - refered to by the
OFFSET)
Properties!B16 = 2

so by my calculation, the formula should return '60'. But it is returning a
zero instead

I'd love to find out why this is happening - any ideas?

Thanks

M

 

[Don Guillett]

Without reading thru this carefully, maybe your offset goes TOO far so that
there is nowhere left to go.

 

[David Biddulph]

It did reach the group earlier:
http://groups.google.co.uk/group/mi..._frm/thread/2c84583cbafe50d0/b07f62c1207f18b8

If the first IF is FALSE, you don't go the second IF, you go to the
alternative result, which you've specified as zero.