Single Cell Array Storage w.o. #SPILL!

Question

Hi!

Is there a way to store an array in a single cell and process the contents of that array in other cells, even if it cannot spill?

Example:

• I have a =RANDARRAY(1000,1) in A1, A2, ...
• I then put =LOGNORM.INV(A1#,1,1) in B1 (and with the corresponding A2# in B2) and so on.
• I then put =AVERAGE(B1#) and so one in row C.

As the RANDARRAY functions can't spill, I get the #SPILL! error. That error then propagates through the formulas.

Nesting the formulas works though (i.e. =AVERAGE(LOGNORM.INV(RANDARRAY(1000,1),1,1)) ). I do want to reuse the arrays from certain steps elsewhere, so this is not really great.

The best idea for a workaround would be a pseudorandom generator LAMBDA with a seed so that I can access the same random array at different parts of the sheet. Still not perfect as I'd need to chain the formulas still which is inconvenient.

Is there a trick I'm overlooking? Or should I suggest this as a feature?

Thanks!

Answer 1

As long as they're clear it works. If you block e.g. A2 with something so that A1 goes #SPILL! it doesn't. That's the point.

Answer 2

Subsequent calculations fail though when the array has a #SPILL!

e.g. put A1 = SEQUENCE(10) and B1=AVERAGE(A1#). This will give you 6.5 in B1. If you now put anything into Cell A2, you get A1 as #SPILL! and B1 goes to #SPILL! as well.

So it seems that subsequent calculations are dependent on the array acutally being displayed.

Answer 3

Ok. You can store the entire array in a single cell and perform calculations on it. However, the display of the array depends on the size of the window and the number of cells it can display. If the array is too large to fit in the current window, the display will spill and show a #SPILL! error.

Answer 4

Thanks! I'm fine with using Dynamic Arrays, i'm just surprised that whether or not the dynamic array can be displayed properly has an impact on whether calculations can run on it or not.

Will do a little sample sheet and suggest.

Answer 5

Hi AcLehmann

I'm AnnaThomas and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

Unfortunately, there is no way to store the entire array in a single cell in Microsoft Excel without using a feature like Dynamic Arrays, which was introduced in Excel 365. The spill error occurs because Excel's current architecture is limited in its ability to process arrays that are too large to fit in a single cell.

You can try breaking up the large array into smaller chunks and processing each chunk separately. Another option is to use VBA to handle the processing and then write the results back to the spreadsheet. However, this would require a different skill set and may not be suitable for everyone.

It might be a good idea to suggest this as a feature request to the Microsoft Excel team.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.