formula code

[nobbyknownowt]

Hi there, hopefully this is simple!! I've asked the same question as the
last entry on my last question but it could get lost t the bottom
there!



I have been given the following code (the solution is
7.51240351575601)

Sub TestiT()
Dim a, b, f, p
a = 1430
b = 1651
f = 7984
p = 600
Debug.Print Find_x(a, b, f, p)
End Sub



Function Find_x(a, b, f, p)
Dim x As Double
Dim t As Double
Dim j As Long
x = 0
t = 1
Do While x t (THIS MAKES A COMPILE ERROR SO I CHANGED TO
COMBINATIONS OF xt,x>t, x<t and x=t )
t = x
x = x + (a + (b - f) * Tan(x) - p * Cos(x)) / _
(f - b + a * Tan(x) - 2 * p * Sin(x))
Loop
Find_x = x
End Function


How do I put this in my workbook?
I have tried pasting it into both the worksheet and a seperate module
and having the formula =find_x(a,b,f,p) in the cell i need the result
but I just get a #value error, when I evaluate the formula I get #NAME
errors for a,b,f,p

By the way values for a,b,f,p are on the sheet with cell references
b2,b3,b4 & b5. If I change a=1430 to a="b2" or a=b2 I still get the
same error.

Have had a look at various resources to find out what I'm doing wrong
here but its difficult with such a wide range of search terms to
enter.

Anybody able to point me in the right direction?

Cheers
Nobby

 

[Dana DeLouis]

Do While x t (THIS MAKES A COMPILE ERROR

Hi. I think something didn't get copied correctly. The program should keep
looping as long as the new 'x does not equal the older (t)emporary value.

I have tried pasting it into both the worksheet and a separate module

Place the function code in a Worksheet Module.

It sounds like you were using range names. Are your names correct?
Try not to use variable 'a' and angle "A", as these are the same to Excel.

I entered this in B7, and it worked ok.:
=find_x(a,b,f,p)

(returns 0.13111617608834 in Radians)

You can also use this, and it worked ok also:
=find_x(B2,B3,B4,B5)

Also, your main equation may be incorrect also in terms of Excel's format.
(ie cos^2(A) ). Try:
=a*COS(B7)+(b-f)*SIN(B7)-p*COS(B7)^2

This returned 0.00000000000000

I made a slight change to the equation that you should use. Excel's
sometimes won't see a 0 as really being a zero, so we need to round the
numbers. This helps prevent endless loops.

Function Find_x(a, b, f, p)
Dim x As Double
Dim t As Double
Dim j As Long
x = 0
Do
t = x
x = x + (a + (b - f) * Tan(x) - p * Cos(x)) / _
(f - b + a * Tan(x) - 2 * p * Sin(x))
Loop Until Round(x - t, 16) = 0
Find_x = x
End Function


As a side note, there are multiple solutions to your angle x, all differing
by 2 Pi.
Suppose you do not want a negative solution.
You could modify that code so that you start your guess at 0, and then make
your best guess before the loop.
ie x = x + (a + (b - f) * Tan(x)...etc
Now, if this is negative, add 2 Pi.
If x < 0 Then x = x + 2 * WorksheetFunction.Pi

Now, continue the code within the loop, and you should get a positive
number.

 

[nobbyknownowt]

Dana
Thanks for your continued attempts to help but I still cannot mak
this work!

1. Do while xt. I have noticed that <> the sybols for greater tha
and less than do not copy/paste into the forum so when I copy out you
code all I get is Do(space)while(space) x(space)t This causes
compile error. (could there be other symbols I am missing??)
I have corrected this by changing t
do(space)while(space)x(space)symbolgreaterthan(space)symbollessthan(space)t

2. I have.

Sub TestiT()
Dim a, b, f, p
a = 1430
b = 1651
f = 7984
p = 600
Debug.Print Find_x(a, b, f, p)
End Sub

In the worksheet by copying the code, opening the worksheet, righ
click the tab,veiw code and paste.

I have

Function Find_x(a, b, f, p)
Dim x As Double
Dim t As Double
Dim j As Long
x = 0
t = 1
Do While x t
t = x
x = x + (a + (b - f) * Tan(x) - p * Cos(x)) / _
(f - b + a * Tan(x) - 2 * p * Sin(x))
Loop
Find_x = x
End Function

In a worksheet module by right click tab, select worksheet in top lef
box, right click and insert module. Paste

3. I have tried all your codes and combinations of changing a,b,f,p fo
the cell entries and get the same error when i evaluate the formula o
the auditing toolbar, That is:

reference : sheet 1 $b$7
evaluation : find_x(a,b,f,p)
"evaluate" : find_x(#NAME?,b,f,p)
: find_x(#NAME?,#NAME?,f,p)
: find_x(#NAME?,#NAME?,#NAME?,p)
:find_x(#NAME?,#NAME?,#NAME?,#NAME?)
:#VALUE!



Its got me stumped, what am I doing wrong???

cheers
Nobb

 

[Dana DeLouis]

Hi. Try this version instead. It's a little better.

Function Find_x(a, b, f, p)
Dim x As Double
Dim t As Double
x = 0 'Start Guess
Do
t = x
x = x + (a + (b - f) * Tan(x) - p * Cos(x)) / _
(f - b + a * Tan(x) - 2 * p * Sin(x))
Loop Until Round(x - t, 16) = 0
Find_x = x
End Function

If you want, send me your email address, and I'll send you my working copy.
Good luck. Post/write back if you still have problems.
--
:>)
Dana DeLouis
Windows XP, Office 2003

I have corrected this by changing to
do(space)while(space)x(space)symbolgreaterthan(space)symbollessthan(space)t

Hmmm. That should work...??

 

[Dana DeLouis]

do(space)while(space)x(space)symbolgreaterthan(space)symbollessthan(space)t

Oh wait. I read that wrong!
It should read:
Do(space) While(space)x(space) "LessThan" "GreaterThan" (space) t

Do While x "<" ">" t

However, do try the newer code though.

In Excel 2003, with a worksheet displayed, look in help for "About
calculation operators"
Then look for the symbol "not equal to."

 

[Dana DeLouis]

Hi. For discussion, these types of problems need to have some type of
checking. We note that different values of a,b,f, & p can make this harder
to solve.
If we make our first guess 0, then we note that for this problem, there is
no need to calculate the big equation for the next value. It simplifies to
just:
x = (p - a) / (b - f)

So, instead of starting out with x=0, it would be better to use x= (p - a)
/ (b - f). This reduces the number of loops by 1.
However, it is now clearer to see that there will be an error if b = f !!
The closer b is to f, then x will tend to be a large number. The code will
zero in on a very large angle x.
Your code will have to adjust if you think b=f, p=a, x is a large number,
....etc.
A slight improvement, but not complete, might be something like this:

Function Find_x(a, b, f, p)
Dim x As Double
Dim t As Double

On Error Resume Next
x = (p - a) / (b - f)
If x = 0 Then x = 1 'try different value

Do
t = x
x = x + (a + (b - f) * Tan(x) - p * Cos(x)) / _
(f - b + a * Tan(x) - 2 * p * Sin(x))
Loop Until Round(x - t, 16) = 0
Find_x = x
End Function

One example might be that if b =f, then the loop equation could be reduced
to :
x = x + (p*Cos(x) - a)/(2*p*Sin(x) - a*Tan(x))

Again, lots of variations to account for different values.

 

[nobbyknownowt]

Hi Dana

If you would be so kind as to send your version to

willium_cobblers
@yahoo.co.uk

Had to split it so it would post!

Hopefully that will get me going again

Thanks again

Does the number of loops used cause a problem