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

 

[Alan Cocks]

Keith
The hash marks just mean the item is too large to display in the cell
either make the cell larger, or under format cells alignment select
"shrink to fit"

Alan

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"),"")))

 

[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!