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

%3CLINGO-SUB%20id%3D%22lingo-sub-1693068%22%20slang%3D%22en-US%22%3EDynamic%20cells%20always%20turning%20to%20N%2FA%20or%20spill%2C%20formulas%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1693068%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20working%20on%20this%20sheet%20for%20hours%20trying%20to%20figure%20out%20what%20my%20mistake%20is%2C%20but%20have%20not%20succeeded.%20Even%20though%20my%20formulas%20are%20correct%2C%20I%20only%20gather%20proper%20numerical%20results%20after%20a%20couple%20of%20times%20of%20updating%20the%20sheet%20(F9)%20but%20then%20it%20suddenly%20changes%20to%20either%20!Spill%20or%20N%2FA.%20The%20mistake%20starts%20with%20the%20cells%20where%20I%20want%20to%20gather%20MIN%2FMAX%20of%20my%20Vlookup%20results.%20I.E.%2C%20the%20problem%20is%20in%20the%20%22BMW%22%20Tab.%3C%2FP%3E%3CP%3EUsing%20the%20newest%20version%20of%20Office%20with%20MacOS.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFile%20is%20attached.%20I'd%20appreciate%20any%20help.%3C%2FP%3E%3CP%3EKind%20regards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1693068%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1693964%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20cells%20always%20turning%20to%20N%2FA%20or%20spill%2C%20formulas%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1693964%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F801725%22%20target%3D%22_blank%22%3E%40rrkumarr%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20Cumulative%20Frequency%20column%20starts%20from%200.0015.%20VLOOKUP%20on%20all%20numbers%20which%20are%20less%20than%20this%20number%20returns%20%23N%2FA%20error.%20You%20need%20-%20or%20start%20this%20range%20from%20zero%3B%20or%20adjust%20random%20numbers%20range%20with%20RANDBETWEEN()%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2)%20Dynamic%20array%20which%20makes%20calculations%20on%20another%20dynamic%20arrays%20with%20random%20numbers%20from%20time%20to%20time%20returns%20%23SPILL!%20error%2C%20that%20is%20specific%20of%20evaluation.%20You%20shall%20avoid%20such%20combinations.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

@rrkumarr 

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

Thank you @Sergei Baklan 

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

 

Thanks

Highlighted

@rrkumarr 

That's like

=RANDBETWEEN(15,10000)/10000

or so, function returns integers between bottom and top.