#REF Error when summing inside a spill range

[lhaggs]

I'm trying to calculate an array of values using a function that spills its output into the cells below it. One of theses cells needs to contain a sum of the numbers above it, but the ways I've tried to do this sum have only resulted in a #REF error in the cell so far.

I've attached a picture of the sheet, and the formula that's causing this issue is =IF(A3#>0,IF(A3#<m,FCI/N,IF(A3#=m,FCI-SUM(INDIRECT(OFFSET(INDEX(A3#,1),1,5,ROW()-3,1))),)),) in cell F3. The other columns G and H which show the #REF error reference column F which is why they show the same error.

 

[Fluff]

Hi & welcome to MrExcel.
Whilst I've no idea what your formula is doing, you will get the #REF error because this part

ROW()-3

equates to 0 if the formula is in row3

 

[lhaggs]

Hi & welcome to MrExcel.
Whilst I've no idea what your formula is doing, you will get the #REF error because this part

ROW()-3

equates to 0 if the formula is in row3

Thanks for the reply! I can see why telling the OFFSET function to make a range with no height would set it off. My #REF error still persist if I change the formula to a different number than 3, and anything other than 3 results in a circular reference, but I have no idea why.

 

[Fluff]

Maybe because the offset is looking at col F, where the formula is.