Forum Discussion
Dynamic cells always turning to N/A or spill, formulas correct
Hello community,
I have been working on this sheet for hours trying to figure out what my mistake is, but have not succeeded. Even though my formulas are correct, I only gather proper numerical results after a couple of times of updating the sheet (F9) but then it suddenly changes to either !Spill or N/A. The mistake starts with the cells where I want to gather MIN/MAX of my Vlookup results. I.E., the problem is in the "BMW" Tab.
Using the newest version of Office with MacOS.
File is attached. I'd appreciate any help.
Kind regards
3 Replies
1) Cumulative Frequency column starts from 0.0015. VLOOKUP on all numbers which are less than this number returns #N/A error. You need - or start this range from zero; or adjust random numbers range with RANDBETWEEN()
2) Dynamic array which makes calculations on another dynamic arrays with random numbers from time to time returns #SPILL! error, that is specific of evaluation. You shall avoid such combinations.
Thank you SergeiBaklan
According to your suggestions, I tired =RANDBETWEEN(0.0015,1) but it only outputs 1. Any further tips?
Thanks
That's like
=RANDBETWEEN(15,10000)/10000or so, function returns integers between bottom and top.
