Solved

Solving equation in excel

Posted on 2014-04-02
10
307 Views
Last Modified: 2014-04-03
Can someone tell me how to solve this problem in excel? Basically the total for yours and mine X and Y are given (see total line) net result is 54.  The net total for Yours is 46 I need to find Yours X and Y. And the net result of Mine is 8 and I need to find Mine X and Y .


                   Net
 Yours        X        - Y        46
                  
 Mine        X        - Y        8
      
                  
 Total        663        -609        54
0
Comment
Question by:chekn3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
10 Comments
 
LVL 42

Accepted Solution

by:
pcelba earned 200 total points
ID: 39973840
Lets substitue Yours X, YoursY to B1 and C1 in Excel  and Mine X, MineY to B2 and C2 respectively.

Your equations are then:

B1 - C1 = 46
B2 - C2 = 8
B1 + B2 = 663
C1 + C2 = 609
B3 - C3 = 54
D1 + D2 = 54

Now enter known cells to Excel sheet:
D1 = 46
D2 = 8
B3 = 663
C3 = 609  (not necessary for calculations because it is dependent on other values)
D3 = D1 + D2  (not necessary for calculations because it is dependent on other values)


Then you may simply iterate the result by following formulas entered into Excel sheet:

B1 = B3 - B2
B2 = D2 + C2
C1 = B1 - D1
C2 = B2 - D2

To allow iterations in Excel go to the Tools - Options - Calculations and set number of iterations to e.g. 100 and Excel will calculate the result for you:

B1 = 1118  (Yours X)
B2 = -455  (Mine X)
C1 = 1072  (Yours Y)
C2 = -463  (Mine Y)
Iteration.xls
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 300 total points
ID: 39973851
I'm not sure this problem is solvable.

I put the coefficients into a matrix in B8:E11 for the four equations with four unknowns:
1      0      -1      0
0      1      0      -1
1      1      0      0
0      0      -1      -1

I then put the numeric values in a column G8:G11:
46
8
663
-609

The values of Your X, My X, Your Y, My Y should then be returned in a column of four cells using the following array-entered formula:
=MMULT(MINVERSE(B8:E11),G8:G11)

I got #NUM! error values using the above array-entered formula.

I tried Solver, and that didn't work either.
SolveSystemEquationsQ28403789.xlsx
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 300 total points
ID: 39973875
I tried Solver using GRG Non-linear, and it gave the following results:
Your X   My X   Your Y   My Y
341            322        295       314

Plugging those values into the formulas checks. As do the values given by pcelba
1118        -455     1072      -463

Since the system of equations has two unrelated set of answers, it wouldn't surprise me if there were more.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 42

Assisted Solution

by:pcelba
pcelba earned 200 total points
ID: 39974428
Yes, if you have four variables but just 3 equations then there is infinite number of solutions. (The 4th equation is just derived from previous three.)

So possible solutions are:

341  322  295  314
342  321  296  313
343  320  297  312
344  319  298  311

etc.
0
 

Author Comment

by:chekn3
ID: 39975395
I'm sorry I did not clarify, Y is not alway negative. The negative sign in front of Y is simply mean  X minus Y = 46. The same goes for all the Y and the total number for Y, it's not -609.
                                                Net
 Yours        X        - Y               46
                 
 Mine        X        - Y                  8
     
                 
 Total        663      -609        54
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 300 total points
ID: 39975936
pcelba multiplied the equation by minus 1, so:
Yours Y + My Y = 609

This is exactly equivalent to:
-Yours Y  +   - My Y = =609

The point remains that there are an infinite number of possible solutions to this particular problem.

If there were four independent equations (not true in this particular situation, as pcelba pointed out), then the matrix inversion and Solver techniques shown in the workbook I posted will both work. I presume the iteration approach that pcelba showed would also work, but that's a lot harder for me to wrap my head around.
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39976919
byundt, could you please tell what equation was multiplied by -1?  I don't see any  :-)

The post ID: 39975395 is clear enough.

One mistake is -609 in the post ID: 39973851 which resulted into "no solution".

Never mind, I don't know how to use the Solver at all...
0
 
LVL 81

Expert Comment

by:byundt
ID: 39976929
pcelba,
In http:/Q_28403789.html#a39973840 you show the equation C1+C2 = 609, but in chekn3's original question, he had -Yours Y +  -My Y = -609

If you multiply checkn3's formula by minus one, the result is your formula. I made a point of mentioning this because checkn3 was complaining about the +609 in your Comment vs. the -609 in his original problem statement. My post was trying to explain that the two formulations were equivalent, but I seem to have only added to the confusion, for which I apologize.

I'm glad you were able to demonstrate the multiplicity of possible solutions. I suspected as much, but didn't know how to prove it.

Brad
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39976952
No apology needed. The two formulations are really equivalent. I've also been thinking twice (at least) about the minus signs meaning (unary minus vs. subtraction) and C1+C2 = 609 was the result.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question