Pressing F9 (results to be in a different cell)

  • Thread starter Thread starter pturyamwijuka
  • Start date Start date
P

pturyamwijuka

Members
Iam in excel whereby pressing F9 generates an answer with the followin
formula '=SQRT($F$31)*$C$30*100' in a cell and when I press F9 again i
overwrites the results. I want the results when I press F9 to be put i
a different cell.

Secondly, can I have a macro for F9 and I just put in the number o
times I want the results to be generated instead of Pressing F
severely times.

I would be very greatfull for the assistance and advice.

Padd
 
I'm guessing that either F31 and/or C30 in
: =SQRT($F$31)*$C$30*100 would contain a formula with
either RAND() or RANDBETWEEN() in it, and that pressing F9
(the recalc key) will generate a new result for the formula

If I've read your intent correctly, here's one way* to set-up
to iterate an output cell of interest (based on randomization)
and get multiple possible results laid out nicely, equivalent
to pressing the F9 key multiple times,
*using a one variable data table

In Sheet1
-------------
Put
In A1: 4
In B1: =10*RAND()
In C1: =INT(SQRT(A1)*B1*100)

C1 is assumed to be the output cell of interest
which value will recalc with each press of F9 key
because of the precedent cell B1 having "RAND()" in it

In Sheet2
-------------
Let's say we want to iterate 100 times
i.e. generate 100 possible values of Sheet1's C1

Put in B1: =Sheet1!C1
(this establishes a link to the output cell of interest in Sheet1)

Put in A2: =ROW(A1)
Copy A2 down to A101

Select A1:B101
Click Data > Table
Leave the Row input cell box empty,
In Column input cell box, enter say: C1
Click OK

In B2:B101 will be generated 100 possible values of
the output cell of interest, Sheet1's C1

Do a copy > paste special > values elsewhere
to freeze the results

And if you want to generate another 100 values,
just press F9 key once

Adapt to suit ..

(.. yes, you can actually set it up to generate up to 65K+
possible values for the output cell at one go,
instead of just 100 <g>)
 
Back
Top