×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel formula problem

Excel formula problem

Excel formula problem

(OP)
Hi i have problem with a formula in excel. It goes like:

=((KVROD((4*(C12^2))/(PI()^4)-((4*C12*(C8^3))/(27*(PI()^2))))-(C8^3/27)+(2*C12/(PI()^2)))^(1/3))-(C8/3)+((C8^2)/(9*((KVROD((4*C12^2)/(PI()^4)-((4*C12*C8^3)/(27*(PI()^2))))-(C8^3/27)+(2*C12/(PI()^2)))^(1/3))))

In mathcad it looks like this:



where; V is G12 and d is C8.

I got the formula working in mathcad, and have to do the same in excel. Can somebody show me a solution? I fear it something with complex numbers to do sad

Please help

 

RE: Excel formula problem

1 - "V is G12" - your formula says C12
2 - what is kvrod?
3 - good luck.  Just need to do the grunt work to check everything carefully.  If needed break the formula into smaller parts and see if they give the required results.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Excel formula problem

Clarification in bold:
3 - good luck.  You just need to do the grunt work to check everything carefully.  If needed break the formula into smaller parts and see if they give the required results.  

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Excel formula problem

One thing that would help some is to name cells, I always find it easier to trouble shoot =pi()/4*Dia^2*Len than =pi()/4*G13*H61.

Another thing that I've had help is to break the equation up into smaller chunks and then at the end I can combine the chunks with considerably fewer parenthesis (I started to try to match your parenthesis but I got lost).

David

RE: Excel formula problem

Yes, I suspect it is an issue with complex numbers. (I assume "KVROD" is the conventional square root function - is that correct?)

For at least some values of V and d, the terms within the square root can yield a negative number, so that the whole square root term is a complex number. Mathcad seems to be able to handle this in its stride, and the complex terms seem to cancel out so that the total solution seems to yield a real number (at least for the few simple examples I have tried). Excel "chokes" when you try to evaluate the square root of a complex number, however.

You might have some success if you use the "IMSQRT" (complex square root) function instead of "SQRT". (You need to install the "Analysis ToolPak" Add-In to have access to complex functions in Excel.)

Hope this helps!

RE: Excel formula problem

Conditional upon what KVROD actually does, your formula seems to be a correct representation of your equation.

It is probably a matter of taste, but for those who find it easier to work with the minimum amount of brackets your formula can be reduced to

=(KVROD(4* C12^2/PI()^4-4*C12*C8^3/(27*PI()^2))-C8^3/27+2*C12/ PI()^2)^(1/3)-C8/3+C8^2/(9*(KVROD(4*C12^2/PI()^4-4*C12*C8^3/(27*PI()^2))-C8^3/27+2*C12/ PI()^2)^(1/3))

 

RE: Excel formula problem

non english countries have different names for the build in versions of the excel functions if their office installation is in the local language (but not in VB). Im Danish and i at the office i have an english office version and at home i have a DK (sponsored by my wifes company so i cant really complian). But its a pain in the butt sitting there trying to remember that SQRT is not SQRT but KVROD. I think the program can convert - at least from ednglish->dk - although the opposite may cause more problems. The guy who thought that up will surely burn in hell.

Best regards

Morten  

RE: Excel formula problem

To avoid complex numbers
d <= 3*(V/pi^2)^(1/3)

RE: Excel formula problem

You could make use of cowski's inequality check in an IF() formula to give the result if it is real.  You could also use the engineering toolpak to handle complex numbers if the inequality does not hold.
 

RE: Excel formula problem

A simple approach is to name the values to match the column.

If C12 is the value in column C, row 12, , then  let v = C12
 Similarly, let d = C8.
Now prepare the equation using those values directly.
Check each component and the first answer .

RE: Excel formula problem

Thanks for the reference even though i would have preferred to have the functions in english smile

Best regards

Morten

RE: Excel formula problem

My suggestion is to create a function and break you equation in pieces.  By doing so, it gets easier to control any long and complex mathematical expression -- just take full advantage of the MS Excel capabilities ...

http://www.engineering-4e.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources