Highlighted
New Contributor

# 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
Highlighted

# Re: Dynamic cells always turning to N/A or spill, formulas correct

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.

Highlighted

# Re: Dynamic cells always turning to N/A or spill, formulas correct

Thank you @Sergei Baklan

According to your suggestions, I tired =RANDBETWEEN(0.0015,1) but it only outputs 1. Any further tips?

Thanks

# Re: Dynamic cells always turning to N/A or spill, formulas correct

That's like

``=RANDBETWEEN(15,10000)/10000``

or so, function returns integers between bottom and top.