Creating automation in Numbers and entering results in unique cells

Question

Level 1

8 points

Creating automation in Numbers and entering results in unique cells

I have Numbers sheet that generates a result based on multiple random number generators that act on various assumptions. All this is working fine but I want to perform multiple trials (2000) to get a variety of results which I can then crunch. Currently, how I do it is, I use the = function to bring the result on Sheet 1 to a second sheet in cell B1. If I enter that number (say 324) manually in cell B2 and press enter the first sheet does a new random calculation and outputs a new result (say 4415) to cell B1 of the second sheet. I can then enter that in cell B3 and press enter, generating a new result - rinse and repeat. The issue is, I have to do this 2000 times which is quite time-consuming and carries the risk of making an error by manually entering the result every time. Additionally, if I want to change any of my assumptions on the first sheet I have to start the process all over again! I want to see all the results of the 2000 trials so that I can then crunch the numbers any way I want. (that part is working fine) Is there any way to automate this process so that it will generate 2000 results and output each one, individually in it's own cell, with just one click? Failing that, I'd even be satisfied with hitting enter 2000 times if that's what it took! Thanks

Posted on Mar 4, 2024 10:47 AM

Reply

Answer 1

Badunit

Level 7

30,708 points

Mar 4, 2024 6:52 PM in response to Todio

It doesn't sound like this is 2000 "trials", it sounds like recursion. You start with a number, perform an operation on it, perform an operation on the result of the first calculation, and so on for 2000 times.

This is super simple if you can get all of your calculations from the first table (the one on Sheet 1) to fit on one row. Below is a really simple version that multiplies the initial value by a random number, then multiplies the result by a random number, and so on. The only thing you might want a "sheet 2" for is to save the results before you do something that makes it recalculate it all again. You would copy column B then Paste Formula Results into a column in another table (that could be on Sheet 2). This will also cause the original table to recalculate if it has any functions like RAND or RANDBETWEEN.

Last row is a footer row.

Column A is just numbers

B2 is an initial value you will enter

B3 = D2

Fill down from there (to row 11)

Copy/paste it to B12

Formula in C2 =RAND(1,3)/2

Formula in D2 =B2×C2

fill down with both

Reply

Answer 2

Todio Author

Level 1

8 points

Mar 5, 2024 12:28 PM in response to Badunit

Badunit, thank you for this, and it is a very useful function, but I don't think it's what I'm looking for. I don't need to perform a new calculation on the result, I want to preserve the result and do the calculation again which will generate a new result, save that, and so on x 2000.

To add some clarity, this is a game show simulator. I use a 1/4 random number generator to determine if a person gets the correct answer "Y" to 24 questions or not "X" using the assumption that, on average, a person will miss 25% of the answers to general trivia questions. This parameter may change.

[ =RANDBETWEEN(1,100) ] (generates a random # between 1 and 100)

[ =MOD(result,4)=0 ] (if the number is divisible by 4 return a 0)

[ =IF(result),"X","Y") ] (if the answer is 0 then print an X otherwise print a Y)

If they answer correctly for the first 8 they get $5 each, for the second 8 it's $10 and for the final 8 it's $15. If they answer incorrectly they don't get money for that question. (These dollar amounts may change). At the end of the 24th question the accumulated money is added up.

Then they have 5 big money questions. If they get any one of them correct they get $500 more, any 2 they get $1000, any 3 they get $2000 and any 4 they get $4000. I do this by using the same random number generator as above then adding the number of correct answers and using IF statements IF(result)=1,$500 IF(result)=2,$1000 and so on

At this point they have the choice (I put the odds at 50% using a random number generator and this parameter may change) to either keep the money or press on to the 5th question for $10,000. If they get that right they get $10,000. If they get it wrong they keep the $ earned at the end of the 24th question or the money they elected to take when presented the choice. The sheet outputs the final payout.

I must stress, all this works but here's where I am stuck.

The final payout is the data I wish to preserve then I want to run the entire thing again, generating a different outcome and so on x 2000. I'd like to have it happen automatically if possible but, as I said, I'd even accept hitting 'enter' 2000 times if it came to that.

Should I change any of the parameters (odds, amount won per question etc.) then I have to re-run all the simulations again manually (2000 times!) which is very tedious and time consuming.

Again, thanks for your help so far and I hope this helps you see the situation clearer. Any further help is greatly appreciated. I can send you my sheet if you want.

Reply