Solved

# Solving equation in excel

Posted on 2014-04-02
293 Views
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
Question by:chekn3
• 4
• 4

LVL 41

Accepted Solution

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

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 80

Assisted Solution

byundt earned 300 total points
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 80

Assisted Solution

byundt earned 300 total points
I tried Solver using GRG Non-linear, and it gave the following results:
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

LVL 41

Assisted Solution

pcelba earned 200 total points
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

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 80

Assisted Solution

byundt earned 300 total points
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 41

Expert Comment

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 80

Expert Comment

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.

0

LVL 41

Expert Comment

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

### Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Viewers will learn the different options available in the Backstage view in Excel 2013.
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…