ttt

[franzpeter]

Hello,

unfortunately it is not possible to upload a file, so I try to explain it as best as I can:

I use =NORM.INV to produce columns of random values like:

[TABLE="width: 800"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Values[/TD]
[TD="class: xl65, width: 80"]Values[/TD]
[TD="class: xl65, width: 80"]Values[/TD]
[TD="class: xl65, width: 80"]Values[/TD]
[TD="class: xl65, width: 80"]Values[/TD]
[TD="class: xl65, width: 80"]Values[/TD]
[TD="class: xl65, width: 80"]Values[/TD]
[TD="class: xl65, width: 80"]Values[/TD]
[TD="class: xl65, width: 80"]Values[/TD]
[TD="class: xl65, width: 80"]Values[/TD]
[/TR]
[TR]
[TD="class: xl66"]26.8538815[/TD]
[TD="class: xl66"]5.45631173[/TD]
[TD="class: xl66"]-51.0077324[/TD]
[TD="class: xl66"]79.4960151[/TD]
[TD="class: xl66"]39.7611752[/TD]
[TD="class: xl66"]-5.85417276[/TD]
[TD="class: xl66"]0.19933706[/TD]
[TD="class: xl66"]36.3109158[/TD]
[TD="class: xl66"]-55.9130755[/TD]
[TD="class: xl66"]-46.7417441[/TD]
[/TR]
[TR]
[TD="class: xl66"]19.0776181[/TD]
[TD="class: xl66"]64.8366344[/TD]
[TD="class: xl66"]-44.4498798[/TD]
[TD="class: xl66"]8.03219761[/TD]
[TD="class: xl66"]-73.8140031[/TD]
[TD="class: xl66"]10.2904382[/TD]
[TD="class: xl66"]-42.6979209[/TD]
[TD="class: xl66"]6.03050914[/TD]
[TD="class: xl66"]58.5401413[/TD]
[TD="class: xl66"]-56.3084451[/TD]
[/TR]
[TR]
[TD="class: xl66"]4.39047624[/TD]
[TD="class: xl66"]59.9762818[/TD]
[TD="class: xl66"]40.6865279[/TD]
[TD="class: xl66"]-44.8519101[/TD]
[TD="class: xl66"]47.4748356[/TD]
[TD="class: xl66"]55.2761705[/TD]
[TD="class: xl66"]-43.4841945[/TD]
[TD="class: xl66"]-34.0451411[/TD]
[TD="class: xl66"]40.9571417[/TD]
[TD="class: xl66"]75.5229871[/TD]
[/TR]
[TR]
[TD="class: xl66"]44.7989651[/TD]
[TD="class: xl66"]-56.3868303[/TD]
[TD="class: xl66"]8.22514414[/TD]
[TD="class: xl66"]-36.3734794[/TD]
[TD="class: xl66"]119.625039[/TD]
[TD="class: xl66"]149.910503[/TD]
[TD="class: xl66"]29.3530674[/TD]
[TD="class: xl66"]25.6213267[/TD]
[TD="class: xl66"]55.5856752[/TD]
[TD="class: xl66"]-3.09640204[/TD]
[/TR]
[TR]
[TD="class: xl66"]21.7888541[/TD]
[TD="class: xl66"]22.6155273[/TD]
[TD="class: xl66"]-71.7567003[/TD]
[TD="class: xl66"]-30.3726047[/TD]
[TD="class: xl66"]7.35397868[/TD]
[TD="class: xl66"]-37.6750579[/TD]
[TD="class: xl66"]36.2415254[/TD]
[TD="class: xl66"]-50.6866414[/TD]
[TD="class: xl66"]-7.07544591[/TD]
[TD="class: xl66"]83.0755513[/TD]
[/TR]
[TR]
[TD="class: xl66"]47.6036592[/TD]
[TD="class: xl66"]94.5247985[/TD]
[TD="class: xl66"]75.6824659[/TD]
[TD="class: xl66"]-56.3281605[/TD]
[TD="class: xl66"]-67.5962421[/TD]
[TD="class: xl66"]15.3664056[/TD]
[TD="class: xl66"]47.7862198[/TD]
[TD="class: xl66"]-51.3547504[/TD]
[TD="class: xl66"]4.77921981[/TD]
[TD="class: xl66"]-73.7810138[/TD]
[/TR]
[TR]
[TD="class: xl66"]107.963969[/TD]
[TD="class: xl66"]-44.7986498[/TD]
[TD="class: xl66"]19.760799[/TD]
[TD="class: xl66"]78.366183[/TD]
[TD="class: xl66"]-9.11769394[/TD]
[TD="class: xl66"]29.7125062[/TD]
[TD="class: xl66"]-68.8502439[/TD]
[TD="class: xl66"]-24.5809575[/TD]
[TD="class: xl66"]14.4002962[/TD]
[TD="class: xl66"]20.2050168[/TD]
[/TR]
[TR]
[TD="class: xl66"]0.99586994[/TD]
[TD="class: xl66"]94.2060914[/TD]
[TD="class: xl66"]86.806993[/TD]
[TD="class: xl66"]42.2206337[/TD]
[TD="class: xl66"]84.0763841[/TD]
[TD="class: xl66"]29.8304161[/TD]
[TD="class: xl66"]-87.7718429[/TD]
[TD="class: xl66"]39.3643671[/TD]
[TD="class: xl66"]37.2497699[/TD]
[TD="class: xl66"]75.1516207[/TD]
[/TR]
[TR]
[TD="class: xl66"]67.2919977[/TD]
[TD="class: xl66"]-36.2620536[/TD]
[TD="class: xl66"]53.2990103[/TD]
[TD="class: xl66"]44.1607761[/TD]
[TD="class: xl66"]-20.9931005[/TD]
[TD="class: xl66"]23.5962428[/TD]
[TD="class: xl66"]-21.414425[/TD]
[TD="class: xl66"]-4.17458175[/TD]
[TD="class: xl66"]29.7556998[/TD]
[TD="class: xl66"]64.432296[/TD]
[/TR]
[TR]
[TD="class: xl66"]9.02666472[/TD]
[TD="class: xl66"]22.2611111[/TD]
[TD="class: xl66"]50.6139662[/TD]
[TD="class: xl66"]20.7483508[/TD]
[TD="class: xl66"]-47.2115601[/TD]
[TD="class: xl66"]41.1012334[/TD]
[TD="class: xl66"]-30.3950436[/TD]
[TD="class: xl66"]-52.8670562[/TD]
[TD="class: xl66"]62.9456079[/TD]
[TD="class: xl66"]-42.8888878[/TD]
[/TR]
[TR]
[TD="class: xl66"]43.3791379[/TD]
[TD="class: xl66"]26.7891076[/TD]
[TD="class: xl66"]8.22455558[/TD]
[TD="class: xl66"]-34.5639249[/TD]
[TD="class: xl66"]-3.28262263[/TD]
[TD="class: xl66"]-22.8775143[/TD]
[TD="class: xl66"]-69.8304546[/TD]
[TD="class: xl66"]-92.9627745[/TD]
[TD="class: xl66"]-14.6273932[/TD]
[TD="class: xl66"]-21.3035731[/TD]
[/TR]
[TR]
[TD="class: xl66"]102.232077[/TD]
[TD="class: xl66"]-53.0051999[/TD]
[TD="class: xl66"]40.8151199[/TD]
[TD="class: xl66"]-67.4846056[/TD]
[TD="class: xl66"]14.7968863[/TD]
[TD="class: xl66"]44.1383743[/TD]
[TD="class: xl66"]-81.5909865[/TD]
[TD="class: xl66"]103.312873[/TD]
[TD="class: xl66"]78.5493199[/TD]
[TD="class: xl66"]-5.51498408[/TD]
[/TR]
[TR]
[TD="class: xl66"]-34.0479123[/TD]
[TD="class: xl66"]-25.885055[/TD]
[TD="class: xl66"]-52.7465259[/TD]
[TD="class: xl66"]12.7435909[/TD]
[TD="class: xl66"]-43.5573836[/TD]
[TD="class: xl66"]-37.0990157[/TD]
[TD="class: xl66"]-18.4488095[/TD]
[TD="class: xl66"]63.0978172[/TD]
[TD="class: xl66"]-22.8850337[/TD]
[TD="class: xl66"]-49.3460674[/TD]
[/TR]
[TR]
[TD="class: xl66"]79.89857[/TD]
[TD="class: xl66"]20.1725486[/TD]
[TD="class: xl66"]116.887492[/TD]
[TD="class: xl66"]49.1896592[/TD]
[TD="class: xl66"]-46.8293044[/TD]
[TD="class: xl66"]16.8340522[/TD]
[TD="class: xl66"]2.30193905[/TD]
[TD="class: xl66"]-25.0458245[/TD]
[TD="class: xl66"]-61.9686291[/TD]
[TD="class: xl66"]8.34629485[/TD]
[/TR]
</tbody>[/TABLE]


Each column represents a curve in a diagram that is always updated, if F9 is pressed.


[TABLE="width: 1262"]
<tbody>[TR]
[TD]It would be great, if you have some hints how the following tasks could be solved.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Task 1: It should be possible to generate thousends of these curves automatically if F9 is pressed without entering thousends of columns manually and without assigning them manually to a diagram after the calculation is finished.[/TD]
[/TR]
[TR]
[TD="colspan: 4"]Task 2: Ideally I can define the number of curves I want manually by entering this numer into a cell and the rest will be done fully automatically after F9 is pressed.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Task 3: All of these curves should be visible in a diagram, i.e. the diagramm changes dynamically depending on the number of curves calculated.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]For the values in the colums a random generator is used.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Task 4: It should be possible to define a bernoulli or another distribution and to define the probability of success via cells in the excel sheet (i.e. without using the excel add-ins to avoid manual configuration).[/TD]
[/TR]
[TR]
[TD]If F9 is pressed the calculations above are then made based on the selected distribution.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task 5: After x (e.g. thousends) of calculations are finished a result list is provided based on conficence levels.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Example: See right side.[/TD]
[TD="colspan: 3"]List provided after calculations are finished:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]It is not clear how these calculations could be done in Excel and how the confidence levels could be achieved.[/TD]
[TD]Confidence (%)[/TD]
[TD]Threshold +/- 50[/TD]
[TD]Threshold +/- 100[/TD]
[TD]Threshold +/- 150[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]50%[/TD]
[TD]70%[/TD]
[TD]20%[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]55%[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]60%[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]65%[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]70%[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]75%[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]80%[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]85%[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]90%[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]95%[/TD]
[TD]60%[/TD]
[TD]30%[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100%[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]Threshold: The percentage of curves the are between + 50 and -50 or +100 and - 100 etc. relative to the number of curves produced (e.g. 10.000) after F9 was pressed.[/TD]
[/TR]
</tbody>[/TABLE]




It would be great, if you have some hints how the tasks could be solved (hints on single tasks are also welcome of course .... so that I can put it all together).

If you need the file please let me know how I could send it ...

Best Regards ...