SOLVED

Spill

Contributor

Hi All,

Hope you are doing Great!
I want to make column C and column G spill, Can anyone please help me?
Please study the formula, Please let me know if you need help in understanding formula!


Thanks

6 Replies

@yugal40 So you have a circular reference happening if you try to use arrays as it has to compute the whole array but can't since the 2 arrays are co-dependent.  There might be an easier way/trick but what I did is create a triangular unity matix (you can see my 'work' on to the right on the sheet in cols M:X) and then used that matrix and MMULT to create that running sum in col H (so you can confirm the answers matched col G).  Then col C was simply a reference to an array output =H2#

best response confirmed by yugal40 (Contributor)
Solution

@yugal40 

Excel 365

To make columns spill, turn them into Tables.

If you want to turn your array into a Table,

yet avoid spills, since Tables don't accept array formulas,

use the indicated formulas with structured references and/or defined names.

No VBA function or macro.

https://www.mediafire.com/file/edogrm50yz2vubh/11_10_21a.xlsx/file

https://www.mediafire.com/file/5s4hhomlw42ou0u/11_10_21a.pdf/file

 

@yugal40 

 

Maybe something like this (columns D and I)? At the moment I'm working on 2016, so the formulas are in the old array style and you'll have to update the range references to create a spill (B2# instead of B2:B13, for example).

I appreciate your efforts but was looking for a #Spill function
Thanks
Thanks for your reply!
To be honest that is something very big formulas, Was thinking to play with #Spill
Thankyou, Learnt something new