Inputting a formula

[maloneb]

Ok guys, this is a bit cheeky as it's not strictly an excel based question.
I'm currently writing a program and have come across an equation that needs rearanging so that it can be input into VBA. Unfortunately i'm experiencing a minor brain-breakdown and am struggling. I think I have an answer but not sure it is correct.

The equation in question is
{(P/Pe)-1}{((Pc/Py)^2)-1}=(Pc/Py)(Fo(Do/Tnom))
and must be rearrange for Pc = ...

Can anyone help?

 

[Jonmo1]

Welcome to the board..

I'm not going to pretend to understand that formula....LOL..

BUT..

The quickest way to convert a cell formula to VBA is to use the macro recorder.

Start recording a macro from Tools - Macro - Record new macro
Give it a name.
Now Enter the formula by hand in a cell as you normally would
Now stop recording.

You now have some VBA code that should give you a good start with the correct VBA syntax.

 

[kpark91]

The final equation of the formula provided was

(P/Pe - 1){(Pc/Py)^2} - (Fo * Do / Tnom)(Pc / Py) - P/Pe + 1 = 0

Does this look familiar to you? :P

 

[maloneb]

Its actually a cheeky question because I'm ok with inputting the formula itself into VBA, its the process of converting the maths. I'm having a brain fart in algebra and need it to be Pc= (some form of quadratic)

 

[maloneb]

I know thats what the boards not for, but it's my last port of call!

 

[kpark91]

Its actually a cheeky question because I'm ok with inputting the formula itself into VBA, its the process of converting the maths. I'm having a brain fart in algebra and need it to be Pc= (some form of quadratic)

Use the quadratic formula to find Pc/ Py from the final equation I've extracted for you (check first) then times it by Py

In case:
quadratic formula is x = -b +&- sqrt(b^2 - 4ac) / 2a
where ax^2 + bx + c = 0

 

[maloneb]

Thanks man, big help

 

[maloneb]

The issue with using the quadratic formula (I now realise after fiddling with other parts of the code) is that it has the + OR -. How do you implement this into VBA?

 

[kpark91]

Well. you would first have to figure out if it will return real values (if you need real-life solution, of course)

If b^2 - 4 * a * c < 0 Then
    Exit Sub
ElseIf b ^ 2 - 4 * a * c = 0 Then
    'there is only one solution
Else
    'there are two solutions
End If

You can't implement the + or - into VBA at the same time.
You would have to validate your result based on your own criteria.

but what you could do is make an array of two elements

Dim result(0 to 1) As Double
result(0) = (-b + sqr(b^2-4*a*c)) / (2*a)
result(1) = (-b - sqr(b^2-4*a*c)) / (2*a)

 

[maloneb]

Ever helpful, cheers man