Yeh thats the results that I should be getting. I can get them if I delete
the cell with the function and retype it each time. I'm thinking that it
might be to do with the rest of the spreadsheet.

I retried it and it worked once (didn't the subsequent 2 tries) and decied
that it had a name error on cell IE. Unsure what might cause this yet will
redo the formula and related cells.

I've started going through each of the formulae (theres quite a lot) and
redoing them.

"Gary''s Student" wrote:

With your data:

=x1calc(0.010477442, 230929464, 7760.7996) yields 172.478790283203
=x1calc(0.010477442, 230929464, 4226.792) yields 233.672973632812


Make sure that you are not going into an infinite loop by setting
i=i-1

--
Gary's Student


"Steve" wrote:

It could well be I suppose. By altering the value the I value will alter at
different times so could well be.

The strange thing is that when I recalculate the formula using the altered
values it works. It just seems to be the transition. I take it that there
shouldn't be a problem with it reclaculating automatically.

I'll try rewriting the program, though only got it working this morning
after puzzling over it for some of yesterday!

"Gary''s Student" wrote:

I can't make it crash... for example:

=x1calc(0.1,0.2,0.3) yields 0.733099997043609
=x1calc(0.4,0.2,0.3) yields 0.608099937438964
=x1calc(0.1,0.2,0.7) yields 0.51609992980957

I suspect that the problem may be related to your changing the value of I
within the For Loop.

Is it possible that the value of I can be getting to 0 or 11? This would
cause a crash.

--
Gary''s Student


"Steve" wrote:

Function X1CALC(IE, JC, JD)

Dim ZZ(9) As Single
Dim V As Single
Dim I As Integer

X1 = 0
I = 1

ZZ(1) = 10000
ZZ(2) = 1000
ZZ(3) = 100
ZZ(4) = 10
ZZ(5) = 1
ZZ(6) = 0.1
ZZ(7) = 0.01
ZZ(8) = 0.001
ZZ(9) = 0.0001

For I = 1 To 9

X1 = X1 + ZZ(I)
V = (IE * (X1 ^ 3)) + (JD * (X1 ^ 2))

If V = JC Then
I = 11
End If

If V < JC Then
If V - JC = 0 Then

If V - JC 0.001 Then
X1 = X1 - ZZ(I)
End If

If V = JC Then
I = 10
End If
Else
I = I - 1

End If
End If

Next I

X1CALC = X1

End Function

"Gary''s Student" wrote:

This is very interesting.

Please post the UDF and we will take a look at it.


--
Gary's Student


"Steve" wrote:

I'm trying to convert an old GW Basic program into a more user friendly and
more updateable spreadsheet. I was getting on fine but now that I have
created and implemented a function into the spreadsheet it crashes everytime
data is changed.

The conversion was fine until I came to a repeating loop that calculated the
value of one variable needed to generate a particular value in another. I
have managed to create a working function that does the job. It requires
three values to be called in which come from named cells [i.e. =X1CALC(IE,
JC, JD) ]. Now elsewhere in the spreadsheet are some pull down menus that
alter some of the formulae being used. Every time that the data changes that
is called into the function Excel crashes. I presume that its because the
function will be trying to update itself and can't for some reason. I have
tried only calculating the values on a press of F9 but it still has no
effect. Excel still crashes.

I had a quick try at re writing the function as a Sub and assigning the
values from the cells IE, JC and JD to variables within the Sub but I
couldn't get that to work let alone asigning the output to a cell.

Can anyone help?