Long-time viewer Dave writes in with a potential bug in Excel. Why does RANDARRAY with a RANDBETWEEN inside evaluate to a #SPILL! error about 10 percent of the time? There is nothing in the way that should be causing the spill error.
The sad news is that this is against the rules. Here is the support article: #SPILL! - Volatile Size
The stranger question: why does it work 90% of the time?
The sad news is that this is against the rules. Here is the support article: #SPILL! - Volatile Size
The stranger question: why does it work 90% of the time?
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2315.
An occasional #Spill! due to volatile size.
Hey, welcome back to the MrExcel Netcast. I'm Bill Jelen. Today: Great question.
Sent in by Dave and this question came up twice this week.
I'm going to build a simple little formula here.
Equal RANDBETWEEN and generate a number between four and 10. And then down here a new dynamic array =RANDARRAY(.
How many rows do I want?
I want whatever is an answer is up there in A1. How many columns? Four.
Let's just do something between 11 and 19.
And integer sure why not? OK now when I press enter.
I get an answer right, so there's six here.
It gave me 6 rows and I'm going to come out here to the formulas tab and I'm going to calculate. It works.
It works, it works.
Calculate again, calculate again, calculate again, calculate again, calculate again.
A Spill error. What the heck?
How did it keep working and then all of a sudden stops working?
I actually have the opposite question for Dave is. Why is it working at all?
Because this is against the rules.
I'll put a link to this support article down in the YouTube comments, but basically here's what it says.
Excel is unable to determine the size of the spilled array because it's volatile and resizes between calculation passes.
Dynamic Array resizes may trigger additional calculation passes to ensure the spreadsheet is fully calculated.
If the size of the array continues to change during these additional passes and does not stabilize Excel will resolve the dynamic array as spill.
Now you're allowed to use things like OFFSET, INDIRECT and TODAY because they don't change values on a recalculation pass.
If this was true, I hate to argue with with Microsoft.
(Actually, I'd love to argue with Microsoft) if this was true.
That thing would be re calculating every single time every time I press Calc Now we should be getting this Spill error so it's super frustrating that it works - what - 90% of the time and then all of a sudden fails?
Like why is it working most of the time, Joe Mcdaid?
That's the great question, so honestly, I've never tried this.
I read early on and you're not allowed to do this, so I never did it, but a few people have been doing it.
Seeing it works really good, you know, and then, bam.
Anyway, so known issue.
Can't do it, it's covered in the book Excel Dynamic Arrays Straight to the Point.
Click that I on the top right hand corner.
And hey, if you enjoy these tips, please click the subscribe button and ring that bell.
Feel free to post any questions or comments in the comments below.
I want to thank Dave for sending that question in and I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
An occasional #Spill! due to volatile size.
Hey, welcome back to the MrExcel Netcast. I'm Bill Jelen. Today: Great question.
Sent in by Dave and this question came up twice this week.
I'm going to build a simple little formula here.
Equal RANDBETWEEN and generate a number between four and 10. And then down here a new dynamic array =RANDARRAY(.
How many rows do I want?
I want whatever is an answer is up there in A1. How many columns? Four.
Let's just do something between 11 and 19.
And integer sure why not? OK now when I press enter.
I get an answer right, so there's six here.
It gave me 6 rows and I'm going to come out here to the formulas tab and I'm going to calculate. It works.
It works, it works.
Calculate again, calculate again, calculate again, calculate again, calculate again.
A Spill error. What the heck?
How did it keep working and then all of a sudden stops working?
I actually have the opposite question for Dave is. Why is it working at all?
Because this is against the rules.
I'll put a link to this support article down in the YouTube comments, but basically here's what it says.
Excel is unable to determine the size of the spilled array because it's volatile and resizes between calculation passes.
Dynamic Array resizes may trigger additional calculation passes to ensure the spreadsheet is fully calculated.
If the size of the array continues to change during these additional passes and does not stabilize Excel will resolve the dynamic array as spill.
Now you're allowed to use things like OFFSET, INDIRECT and TODAY because they don't change values on a recalculation pass.
If this was true, I hate to argue with with Microsoft.
(Actually, I'd love to argue with Microsoft) if this was true.
That thing would be re calculating every single time every time I press Calc Now we should be getting this Spill error so it's super frustrating that it works - what - 90% of the time and then all of a sudden fails?
Like why is it working most of the time, Joe Mcdaid?
That's the great question, so honestly, I've never tried this.
I read early on and you're not allowed to do this, so I never did it, but a few people have been doing it.
Seeing it works really good, you know, and then, bam.
Anyway, so known issue.
Can't do it, it's covered in the book Excel Dynamic Arrays Straight to the Point.
Click that I on the top right hand corner.
And hey, if you enjoy these tips, please click the subscribe button and ring that bell.
Feel free to post any questions or comments in the comments below.
I want to thank Dave for sending that question in and I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
