#REF Error when summing inside a spill range

lhaggs

New Member
Joined
Jan 14, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
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.
 

Attachments

  • excel snip.PNG
    excel snip.PNG
    49.1 KB · Views: 7

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,559
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Whilst I've no idea what your formula is doing, you will get the #REF error because this part
Excel Formula:
ROW()-3
equates to 0 if the formula is in row3
 

lhaggs

New Member
Joined
Jan 14, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
Hi & welcome to MrExcel.
Whilst I've no idea what your formula is doing, you will get the #REF error because this part
Excel Formula:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,559
Office Version
  1. 365
Platform
  1. Windows
Maybe because the offset is looking at col F, where the formula is.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,563
Messages
5,637,067
Members
416,956
Latest member
mitzhaki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top