#SPILL!-errors are driving me nuts.

Question

Hello all. I'm pulling my hair out again over Excel returning a SPILL error for a perfectly normal function (No arrays etc.). What's going on here?

=IFERROR((1-MIN(ABS($B8)/3,1))*B3+MIN(ABS($B8)/3,1)*INDEX($B3:$F3,1,COLUMN(B3)-COLUMN($B3)+1-SIGN($B8)),0)

Is there a way to just switch off SPILL entirely?

/A

hansvogelaar · Answer

If COLUMN(B3)-COLUMN($B3)+1-SIGN($B8) evaluates to 0, INDEX will return the entire row $B3:$F3, causing your formula to return 3 cells.

sergeibaklan · Answer

You may use=@IFERROR(
  (1-MIN( ABS($B7)/3,1) )*B2 +
  MIN( ABS($B7)/3,1 ) *
      INDEX($B2:$F2, 1, COLUMN(B2)-COLUMN($B2)+1-SIGN($B7)),
 0)or=IFERROR(
  (1-MIN( ABS($B7)/3,1) )*B2 +
  MIN( ABS($B7)/3,1 ) *
      INDEX($B2:$F2, 1, MAX(1, COLUMN(B2)-COLUMN($B2)+1-SIGN($B7) )),
 0)

Forum Discussion

#SPILL!-errors are driving me nuts.

2 Replies

Resources