Solving equation in excel

Posted on 2014-04-02
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 .

 Yours        X        - Y        46
 Mine        X        - Y        8
 Total        663        -609        54
Question by:chekn3
  • 4
  • 4
LVL 41

Accepted Solution

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)
LVL 81

Assisted Solution

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:

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:

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

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

Assisted Solution

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.
LVL 41

Assisted Solution

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


Author Comment

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.
 Yours        X        - Y               46
 Mine        X        - Y                  8
 Total        663      -609        54
LVL 81

Assisted Solution

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.
LVL 41

Expert Comment

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...
LVL 81

Expert Comment

ID: 39976929
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.

LVL 41

Expert Comment

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.

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now