1 registered members (1 invisible),
259
guests, and 3
spiders. |
Key:
Admin,
Global Mod,
Mod
|
|
Forums69
Topics113,624
Posts1,341,348
Members1,807
|
Most Online731 Jan 14th, 2020
|
|
|
Re: Excel Question
[Re: szkom]
#1498894
16/07/2014 14:48
16/07/2014 14:48
|
Joined: Dec 2005
Posts: 12,546 Northumberland
AndrewR
I AM a Coop
|
I AM a Coop
Joined: Dec 2005
Posts: 12,546
Northumberland
|
No, sorry, it can only be done with macros.
On the plus side, it's very easy to do.
Dear monos, a secret truth.
|
|
|
Re: Excel Question
[Re: szkom]
#1498921
16/07/2014 18:51
16/07/2014 18:51
|
Joined: Dec 2005
Posts: 8,417 Lightwater, Surrey
DaveG
Club Treasurer Member 311
|
Club Treasurer Member 311
Je suis un Coupé
Joined: Dec 2005
Posts: 8,417
Lightwater, Surrey
|
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?
1996 Portofino 20vt & 2000 Pearl White Plus 1985½ & 2016 2017 Fiat 124 Spider + XF Sportbrake
|
|
|
Re: Excel Question
[Re: szkom]
#1498923
16/07/2014 19:59
16/07/2014 19:59
|
Joined: Dec 2005
Posts: 12,546 Northumberland
AndrewR
I AM a Coop
|
I AM a Coop
Joined: Dec 2005
Posts: 12,546
Northumberland
|
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.
Dear monos, a secret truth.
|
|
|
|