Excel - Equating two equations

[MiguelPenaWSE]

Hi there!
I'd like some assistance with Excel.

I'm designing a prestressed I Girder.
I'm trying to equate two equations to solve for two unknowns in excell.

The equations are.

-(P/A) + (P x e / Stop ) - (Mmin / Stop) <= Limit @ top

-(P/A) - (P x e / Sbot ) + (Mmin / Sbot) <= Lmit @ bottom

Stop = 216,600,000 mm3
Sbot = 220,200,000 mm3
A = 499,000 mm2
Mmin = 922,000,000 N-mm
Limit @ top = 1.369 MPa
Limit @ bot = 18.0 MPa

P = prestressing force in tendon
e = eccentricity at mid span

I've solved it by hand twice. Please see below
Trial #1
P = 4,189,263 N
e = 725 mm

Trial#2
P = 4,866,760 N
e = 184 mm

I'd like to know how to write these equations in excel so I can get an accurate answer for both unknowns.
I saw a few threads similar to this one but in those there was only one unknown per fraction... In my case I have two unknowns in one franction (P x e / S).

I appreciate your help.

Thanks

 

[Denial]

You do not have two EQUATIONS.[&nbsp;] You have given us two INEQUALITIES.[&nbsp;] In general these will have an infinitude of possible answers, of which you have come up with two.[&nbsp;] You will need some criterion for selecting which out of the infinitude is best for your particular circumstances.

 

[MiguelPenaWSE]

Thank you very much for your quick reply.
I would guess I'm looking for the maximum values for "P" and "e" so both equations can be validated.

The left hand side of the equation must be less or equal to the right hand side.
When I solved it, I assumed the left side to be equal to the right side.

I equated EQ1 to EQ2... Solved for "P" in terms of "e"... Then plug "P" into EQ1 and solved for "e"... Then went back and solved for "P".

Is it possible to solve it in Excel?

Thanks!

 

[IDS]

You can do it in Excel, but you need to look at the sign conventions.

If you turn your two limits into an equation you can rearrange to give:

e_1 max = (Limtop + P/a + Mmin/Stop)*Stop/P
e_2 max = -(Limbot + P/a - Mmin/Sbot)*Sbot/P

which will give the maximum eccentricity for any given P and stress limits, but the sign convention is compression negative and tension positive, so the value of Limbot needs to be -18, not 18, and the second limit should be >= rather than <=.

Making that change, a P of 4189 kN gives an eccentricity limit of 725 mm as you found, but for 4867 kN I get limits of 684 mm and 562 mm. The lesser of the two gives a top stress of -1.38 MPa, but this is compressive, so nowhere near the limit of 1.37 MPa tension. An eccentricity of 684 mm brings the top stress up to the tension limit, but the bottom stress exceeds the compression limit.

Finally, if you are not familiar with Magnel diagrams, look them up, they make the process of optimising the prestress force and eccentricity much easier.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Mutt]

You can use a solver function to maximise P, varying P and e such that the calculated top and bottom limits equal the set maximum values in the solver constraints. That gives the extreme values of P and e, not necessarily the optimum though....the solver solution then gives P as 4189263 and associated e as 724.9. As other commentator stated, an infinite number of solutions is use the less than function.

 

[MiguelPenaWSE]

IDS, thank you very much for your information.
I believe I understand the logic behind the Magnel Diagrams. The shaded area between the 4 lines (equations) will give a range of possibilities for "P" and "e".
I will not be able to use this method in an exam because I will not have sufficient time to graph everything.
The reason I would like to know how to solve this in excel is so I can check my hand calculations and make sure I am doing my algebra correctly.

Mutt, thank you very much for your input.
I didn't know excel had a Solver Function. After watching some videos on YouTube, I have yet to figure out how to write these system of equations in excel.

What would go into "Set Objective"?
I checked the "Max" box.
and added the two constrains (after changing the signs and the <= to >=)

I just don't know how to write this in excel.
Every video I have watched has had a single unknown in each section of the equation
Ex: 5A + 3B = 6 ... 3A +8B = 11 ... Solve for "A" and "B"

But for my case, I have two unknowns in a one section of my equation.
Ex: -(P/A) + (P x e / Stop ) - (Mmin / Stop) <= Limit @ top ... Where "P" and "e" are unknowns and are part of one fraction of the equation together.

If you guys could perhaps break this down for me, I would appreciate it tremendously.

Thank you very much.

 

[IDS]

You can use the solver, but plotting the Magnel diagram will give you a better picture of what is happening (see attached spreadsheet).

Your equations for top and bottom stress can be rearranged to give maximum eccentricity for any given prestress force (see my previous post and the attached file).

If you calculate the maximum eccentricities for two prestress forces (lower and upper bounds on the possible range of forces you are interested in) and plot these points on a graph of eccentricity v prestress force, that is half a Magnel diagram.

If you are wanting to find the prestress force which has equal eccentricity for both limits, you can either:
- Calculate the equations of the two lines and solve the resulting simultaneous equation to find the intersection point.
- Use the Excel solver to do the same thing
- You don't actually need the Solver, Enter the difference between the two eccentricities, as a formula, in a cell, then use the Goalseek function to adjust the prestress force so that the difference is zero.

Finally - student posts aren't actually allowed here. I think that's a shame myself, but that's the way it is, so make a note of anything you find useful as the thread is likely to be deleted at some stage.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MiguelPenaWSE]

IDS,

Thank you very much for your help.
I was able to get Excel to work and the results are matching my hand calculations.

I didn't mean to do anything that wasn't allowed.
But just to put it out there, I am a Junior Engineering in Ontario. I just happen to be taking some classes towards my masters.

Have a great evening.

Thanks again!

 

[IDS]

Miguel - OK, I'm sure that a young practicing engineer wanting to get a better understanding of prestress design is well within the rules.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/