• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Solving equation in excel

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
chekn3
Asked:
chekn3
  • 4
  • 4
5 Solutions
 
pcelbaCommented:
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
 
byundtCommented:
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
 
byundtCommented:
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
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
pcelbaCommented:
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
 
chekn3Author Commented:
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
 
byundtCommented:
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
 
pcelbaCommented:
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
 
byundtCommented:
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
 
pcelbaCommented:
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now