Excel formula problem 1

[ANNIS]

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 :-(

Please help

 

[electricpete]

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.

 

[electricpete]

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.

 

[zdas04]

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

 

[jhardy1]

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!

 

[Denial]

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
[tt]
=(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))
[/tt]

 

[MortenA]

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

 

[Clyde38]

You might try ExcelCalcs (see below) to help with your "grunt" work. .

http://www.excelcalcs.com/

[link

http://www.linkedin.com/in/clydehancock

]

[/url]

 

[cowski]

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

 

[jghrist]

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.

 

[queque]

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 .

 

[joerd]

@mortenA

This may be helpful: an up-to-date dictionary for Excel 2007 functions with 14 languages:

http://www.piuha.fi/excel-function-n...languages.html

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[MortenA]

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

Best regards

Morten

 

[cherry2000]

see if this helps. Refer to the attached file.

 

[FOURe]

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