Bit of a strange one. I'm after automatically logging numbers that are generated when the spreadsheet recalculates.

For example cell A1 will change every time I hit F9. I want to record those numbers.

Can it be done without Macros? My excel skills are distinctly average.

Thanks in advance

No, sorry, it can only be done with macros.

On the plus side, it's very easy to do.

Well I've almost got something to work without macros.

Start with a blank sheet, and first of all go to Options..Formulas and under Calculation options make sure "Automatic" (Workbook calculation) is selected and "Enable iterative calculation" is ticked, but enter the "Maximum Iterations" as 1, then click OK.

I had to make up an iterative function, in this case it's finding the square root of 2 (deliberately overshooting each time).

In A1 to D1 enter the labels "iter'n", "guess", "error" and "next guess"

1. In C2 enter =B2^2-2

2. In D2 enter =B2-C2/1.5

3. In A4 enter the label "iter'n"

4. In _B4 enter =IF(A4=$A$2,$B$2,_B4) [remove the _ this stops the autocorrect to "before"]

5. In A5 enter the value 1 and in A6 enter the value 2. Highlight cells A5:A6 and drag the bottom right black square down to create the values 1-50 in cells A5:A54

6. Copy cell _B4 to cells B5:B54 [remove the _ this stops the autocorrect to "before"]

7. In B2 enter the value 1

8. In cell A2 enter =A2+1

9. In cell B2 enter =D2

You've now set up a table showing the results of the first and second iteration.

10. Now press F9 repeatedly and the iterative values will fill up the cells B7:B54 and replace the values of 1 that are in there.

To start again, you need to repeat steps 6-10. There's a way to do some of this re-setting automatically (without macros) but let's see if that's kind of what you're looking for?

Ooo, *nice* solution. Three tweaks:

1. In step 4 why not start at cell B5? It's neater.

2. To 'reset' just delete the value in A2 and then re-enter the formula.

3. I also selected the rows 5:54 and used conditional formatting, using the formula =$A5>$A$2, to set the font to white, so that the rows 'appear' as you recalculate.

But, yeah, really nice solution.

Gents, I don't know what to say? Thank you. It'll take me a little while to get my head around that, but it looks like the business.

What I'm trying to do is for my own benefit really. I'm hugely out of touch with Excel's capability, mainly due to the industry and it's seeming lock down on macros. So I've never had the need to do anything more complicated than basic tabulation in a spreadsheet.

That's now changed, so wait for it...I'm building a lottery number generator. It's a bit daft but I plan on capturing the numbers generated and *trying* to generate some form of analysis.

I know there's no real world application for it, just something to get me thinking about problems.