Forum Discussion
How do I solve for an unknown variable from an equation using solver in Excel?
Hello, my name is Stephen Lanford and I am very new to using Microsoft Excel. I am currently trying to solve a problem in a physics class in which I am required to use Excel to solve for an unknown variable in the equation for acceleration of an object in simple harmonic motion. The equation is a(t)=-A(2pi/T)^2 times (cos(2pi/T)(t)), where a is acceleration, t is time, A is amplitude, and T is the period of oscillation. The values of t (3.45 seconds), of a (1.89 m/s^2), and of A (2.34 m) are given. I need to solve for T, which will describe the period of oscillation in seconds.
My text tells me not to "waste time trying to rearrange this equation for the variable of interest (T)" and this equation is so complex that I quite frankly could not figure out how to arrange this equation as "T= everything else" anyway.
The problem is that when I plug this equation into Excel, I cannot seem to find any way to solve for T unless I actually have one of the boxes set for T= something. Unless I have a definite value or direct expression for T, it displays a message that I am committing a division by zero error, since the unknown T is divided by twice in the equation
a(t)=-A(2pi/T)^2 times (cos(2pi/T)(t))
I am sure that there is a way to set things up in Excel so as to make it possible for me to solve this problem and find a value for the period of oscillation. That is why I am now asking the experts!
Attached to this message is a link to my current worksheet on Excel for this problem, so you can tell me what I am doing wrong and need to rearrange. Also attached is the worksheet from my physics class that asks me to do this problem with Excel (note that it is the 2nd of the two problems given in the two page worksheet).
Thanks a million for your help with this problem; I couldn't survive without it!
- Stephen Lanford
4 Replies
And here is an attachment of the page from my assignment, so you can see what I am being asked to do in this problem (remember it is the 2nd problem out of two).
2 things, first are they trying to be cute, as that pdf says 'dont not waste your time'
Second, try this. On your spreadsheet, change b1 to any number, doesn't matter, just so there is no error in D2
Go to the data ribbon and click What-if analysis and goal seak. Fill it out as follows:
Set cell: D2
To value: 1.89
By Changing cell: B4
click ok, it will do it's thing and then give you an answer (1.06034391953358).
Is that what you need?
Thanks, Phillip, for your very helpful reply! However, I forgot to mention my assignment states that there are actually six correct answers for this equation.
"Step 3: Determine six correct periods and submit the complete spreadsheet with six correct answers included to the instructor."
I assume that multiple correct values in this case is simply due to the oscillating nature of the cosine function. However, I am not sure how to set things up to solve for six correct solutions in Excel!
Also, using Solver and setting D2 as the objective (to a value of 1.89, by changing variable cell B4), I got an answer of T = 1.256897 s. This turned out to be at least one of the six correct values for T, as I plugged it into the equation with my calculator and saw the equation multiplied out very close to 1.89. (I also plugged in the 1.06 value of T, but found the equation multiplied out to 2.34, which is the value of A, not of a(t).)
Anyway, do you know how I can set things up using Solver and Excel so that it will show six correct values for the period described by this equation? I have attached the assignment sheet again so you can take another look at what the problem is asking me to do.
Thanks in advance for your invaluable help and advice!
- Stephen Lanford
