complex array formula: help! range change works only sometimes

[Keith R]

I have the following two array formulas, which I put together to test, and
work just fine:

=(SUM(IF(ROUNDDOWN(INDIRECT(B38 & "!F2:F14"),0)=A38,INDIRECT(B38 &
"!G2:G14")-INDIRECT(B38 & "!F2:F14"),"")))

=(SMALL((IF(ROUNDDOWN(INDIRECT(B38 & "!F2:F14"),0)=A38,INDIRECT(B38 &
"!G2:G14")-INDIRECT(B38 & "!F2:F14"),"")),COUNT(IF(ROUNDDOWN(INDIRECT(B38 &
"!F2:F14"),0)=A38,INDIRECT(B38 & "!G2:G14")-INDIRECT(B38 &
"!F2:F14"),""))*0.8))


However, rows 2:14 were only my test data array, in reality I have much
more data. So I decided to expand the number of rows, which I didn't think
would have an effect. However,....

When I change the first one just by adding a zero to each range, to make it
2:140, it just fills the cell with hashmarks (####)

=(SUM(IF(ROUNDDOWN(INDIRECT(B38 & "!F2:F140"),0)=A38,INDIRECT(B38 &
"!G2:G140")-INDIRECT(B38 & "!F2:F140"),"")))


on the second one though, I can add the extra range and it works just fine:
=(SMALL((IF(ROUNDDOWN(INDIRECT(B38 & "!F2:F140"),0)=A38,INDIRECT(B38 &
"!G2:G140")-INDIRECT(B38 & "!F2:F140"),"")),COUNT(IF(ROUNDDOWN(INDIRECT(B38
& "!F2:F140"),0)=A38,INDIRECT(B38 & "!G2:G140")-INDIRECT(B38 &
"!F2:F140"),""))*0.8))



Any ideas what might cause this? I've redone it from scratch several times
to eliminate the possibility of typo errors, so it is something about the
formula, and I have no idea what would cause this.

I have similar formulas in other cells- one exactly the same as the one
that messes up above (SUM) except it uses (AVERAGE) as the keyword. I also
have a variety of shorter formulas that work just fine with the expanded
range, e.g.:

=SUM((IF(ROUNDDOWN(INDIRECT(B38 & "!F2:F140"),0)=A38,1,0))*(IF(INDIRECT(B38
& "!G2:G140")-INDIRECT(B38 & "!F2:F140")<=0.00347222222222222,1,0)))

Any help, prayers, or general ideas and suggestions greatly appreciated- I
have to get this working!

Keith

 

[Keith R]

Alan-

That helped me narrow down the problem enough to figure out that the reason
for the hashmarks was that the returned number was negative, in a cell
formatted as time.

I've looked at the source data, and when I calculate directly on that
sheet, there are no negative numbers, so now I'm stuck again, but I'll have
to play around to narrow the problem down far enough to make a coherent
post.
Thanks!