Using EXCEL to calculate forces in wedges

Hello everyone,

So I have a problem that I cannot seem to get solved in Excel. I am looking to set up a spreadsheet to allow me to input angles, friction coefficients, and weights of wedges to get the force required to move the objected wedge. Everything was going well until I tried solving for N₂ and N₁. Here is the formula I need to solve:

μ = 0.25

-700 - μN₁ - μN₂(.156) + N₂(.988) = 0

given it's a two variable function, we can solve for N₁ with the variable N₂ and substitute that equation in, so we are left with only N₂.

N₁ = μN₂(.988) + N₂(.156) = 0.403N₂

So...

We plug that back into the equation, using the cell N₁ because it will be variable and change, we cannot simply input "μ*0.403N₂"

-700 - μ(N₁) - μN₂(.156) + N₂(.988) = 0

However, since N₁ uses N₂ in the formula, and same with N₂ referencing N₁, there seems to be a loop I cannot get around. On paper, I can solve for N₂ in a heartbeat and finish the equation. Does anyone know how to get Excel to solve that last equation? Thank you!

Hey... I think you have some housekeeping issues with the math first...
1) You dropped the -700
2) You lost the "-" sign on the µN2(.156)

LittleMath.jpg

Now... You have 1 equation and 2 unknowns. The only way this can be done is with substitution... You pick the N2 value and you can calculate the N1 value.

Setup a column with N2 values, use the derived formula above, and solve for n1 for each of the N2 values.

Hope I understood what you were trying to do. If not, scan the statics problem and I will check it out.

Signed... Lehigh BSME, '87

Thank you for the replies.

Here's the solved equation for the problem. You're right Steve, I forgot to include the (-), my mistake. The N₁ is not solved for -700 in this scenario though.

MrShorty, when I get to the "-700 - μ(N1) - μN2(.156) + N2(.988) = 0" part, I can simplify it down to "-700 -0.25(0.403N₂ + 0.949N₂ = 0"

This simplifies to 0.848N₂ = 700 when we solve for N₂ (Last block of work in the attached photo).

Richard, if I understand you correctly: the first. N₁ will be 0.403N₂ and we will use this to solve for N₂ and later, N₁. I have tried Solver, but I just applied the addon today and have virtually no experience with it.

Thank you all again for the help, I've been struggling with trying to get this into Excel all morning.

wedge.JPG

Hi Ryan...
I suggest you forget about excel and understand the Statics first. You must do the problem and understand it before you put it into excel
1) This problem is Static = meaning no motion (no acceleration) So, 1st law, ΣF = MA... Static means A=0 therefore ΣF=Mx0 = 0
2) I cannot see F1 and F2 in the image above.

So, here is your homework for the forum. I would like to see your free-body diagrams for each block. Using your free-body diagrams, sum the forces in each the X and Y direction. Pick a sign convention and use it (housekeeping). Based on this, you will solve the equations simultaneously.

Once you can do it by hand, then you will understand it and putting it into excel will be simple.

Seems to me that you don't quite get the freebody diagram - this is where you get your equations - sum forces in each direction (x direction is 1 equation and y direction is the 2nd equation - this will be for each block. Which means you have 4 equations.

Lehigh University.