Topic: Converting one formula from Excel to AutoLISP

[Tharwat]

Hello,

I am trying to convert one formula that I use in Excel and it gives the result correctly but when converting it to AutoLISP it does not come up with the correct result although the value up to the point to multiply to PI is correct.

Code - Auto/Visual Lisp: [Select]

1. (defun dtr (a) (* pi (/ a 180.0)))
2. (defun rtd (a) (/ (* a 180.0) pi))
3.  
4. ;;  ACOS Returns the arccosinus of a number in radians
5. ;; https://www.theswamp.org/index.php?topic=11561.msg145164#msg145164
6. (defun ACOS (num)
7. ;; Author : gile
8.   (if (<= -1 num 1)
9.     (atan (sqrt (- 1 (expt num 2))) num)
10.   )
11. )
12.  

Formula from Excel.

Code: [Select]

=PI()*2*2000*(180-(DEGREES(ACOS(RADIANS(DEGREES(4000/2/2000))))*2))/360+123+100

Returns correctly : 6506.185

Converting it to AutoLISP.

Code - Auto/Visual Lisp: [Select]

1. (* pi (* (* (* (/ (- 180 (rtd (* (acos (dtr (rtd (/ (/ 4000.0 2.0) 2000.0)))) 2.0))) (+ 360.0 100.0 123.0)) 2000.0) (* pi 2.0)) 2000.0))
2.  
3. Returns incorrectly : 2.43778e+07
4.  

Any suggestion ?

Thank you.

[JohnK]

I dont understand the formula or where these numbers come from but lets clean up the parens and answer some basic questions.
I converted for easier reading.

Also *, / and etc will accept multiple args so you can remove some of those function calls.

So here are my initial questions.

Code - Auto/Visual Lisp: [Select]

1. (/ (* 12566.4
2.       (- 180    ; this will always return 180?
3.          (* (atan (sqrt (- 1 (expt 1 2))) 1) 2.0) ; This will always return 0?
4.          )
5.       )
6.    583 )

[Tharwat]

Thanks John,

Even though the return of your commented codes always come up with static value, the result still incorrect and not matching the same outcome of Excel formula.

[VovKa]

Code: [Select]

(+ (* pi
      (/ (- 180
    (rtd (* (acos (dtr (rtd (/ 4000.0 2.0 2000.0)))) 2.0))
)
360.0
      )
      2000.0
      2.0
   )
   123
   100
)


[Tharwat]

Perfect.
Thank you VovKa.

[Lee Mac]

This seems redundant -

Code - Auto/Visual Lisp: [Select]

1. (dtr (rtd ... ))

Also, this -

Code - Auto/Visual Lisp: [Select]

1. (* 2.0 pi (/ (- 180 (rtd ...)) 360.0))

Is equivalent to:

Code - Auto/Visual Lisp: [Select]

1. (- pi ...)

And so the formula may become -

Code - Auto/Visual Lisp: [Select]

1. (+ (* (- pi (* (acos (/ 4000.0 2.0 2000.0)) 2.0)) 2000.0) 123 100)

I'm assuming that the numbers (/ 4000.0 2.0 2000.0) are replaced by variables, otherwise this will of course always evaluate to 1.0, and hence (acos 1.0) will always evaluate to 0.0; if not, then the formula can become -

Code - Auto/Visual Lisp: [Select]

1. (+ (* pi 2000.0) 223)

[Keith™]

It seems you already have a working solution, but I'm thinking that perhaps the order of operations was throwing it for a loop ... I'll explain.

The excel formula:
=PI()*2*2000*(180-(DEGREES(ACOS(RADIANS(DEGREES(4000/2/2000))))*2))/360+123+100

Evaluated L to R using the order of operations ...
PEMDAS

Parenthesis evaluated FIRST
Then EXPONENTS (you have none)
Then MULTIPLICATION and DIVISION
Then ADDITION and SUBTRACTION

=PI()*2*2000*(180-(DEGREES(ACOS(RADIANS(DEGREES(4000/2/2000))))*2))/360+123+100
=PI()*2*2000*(180-(DEGREES(ACOS(RADIANS(DEGREES(4000/2/2000))))*2))/360+123+100
=PI()*2*2000*(180-(DEGREES(ACOS(RADIANS(DEGREES(4000/2/2000))))*2))/360+123+100
=PI()*2*2000*(180-(DEGREES(ACOS(RADIANS(DEGREES(4000/2/2000))))*2))/360+123+100
=PI()*2*2000*(180-(DEGREES(ACOS(RADIANS(DEGREES(4000/2/2000))))*2))/360+123+100
=PI()*2*2000*(180-(DEGREES(ACOS(RADIANS(DEGREES(4000/2/2000))))*2))/360+123+100
=PI()*2*2000*(180-(DEGREES(ACOS(RADIANS(DEGREES(4000/2/2000))))*2))/360+123+100
=PI()*2*2000*(180-(DEGREES(ACOS(RADIANS(DEGREES(4000/2/2000))))*2))/360+123+100

It ultimately becomes

=PI()*2000+223


Your LISP code incorrectly adds 360 to 123 and 100 to be evaluated before being multiplied

Code - Auto/Visual Lisp: [Select]

1. (+ 360.0 100.0 123.0)

It is easy to get these kinds of things wrong when you have so many things going on, but if there is ever a doubt just break it down from the innermost set of parenthesis and use the PEMDAS order of operations.

Also, as expressed elsewhere, converting values to radians (DEGREES function) then immediately converting back to radians (RADIANS function) is redundant and you could simplify it to

=PI()*2*2000*(180-(DEGREES(ACOS(4000/2/2000))*2))/360+123+100

Good luck!

[mhupp]

See my post in this thread. https://www.theswamp.org/index.php?topic=57236.0
Has a nice little lisp to convert formulas in to lisp.

Code - Auto/Visual Lisp: [Select]

1. (str2prefix "PI()*2*2000*(180-(DEGREES(ACOS(RADIANS(DEGREES(4000/2/2000))))*2))/360+123+100")
2. (+ (+ (/ (* (* (* (* PI NIL) 2) 2000) (- 180 (* (* DEGREES (* ACOS (* RADIANS (* DEGREES (/ (/ 4000 2) 2000))))) 2))) 360) 123) 100)

[Tharwat]

Thank you guys for your precious replies that covered this thread more than I expected honestly.

I am very grateful to you all.

[d2010]

Mr.Tharwat  I need help from you?

Code: [Select]

(Defun Topo (/ $rr BA9 BF8 BF13 BF18)
BA9=BB8+BC7+AY7;
BF8=DEGREES(ATAN((BL6-BL7)/(BK6-BK7)))*200/180+100*ROUND(BE7/100,0)+100*( (BL6-BL7)*(BK6-BK7)
BF9=ATAN((BL5-BL7)/(BK5-BK7))/A21+200;
BF13=ATAN((BL6-BL8)/(BK6-BK8))/A21+200;
BF18=ATAN((BL10-BL9)/(BK10-BK9))/A21+400;
*/
)
Can we work on yourExcel, in this way?
How to export the Excel-Sheet to entire-program source?
Can you share with me, how to work together on your-Excel (.xls)?
How to convert many formulas isinside ExcelSheet to one-programe?