Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Solving equation in excel

Posted on 2014-04-02
10
Medium Priority
?
308 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 43

Accepted Solution

by:
pcelba earned 800 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 1200 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 1200 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 43

Assisted Solution

by:pcelba
pcelba earned 800 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 1200 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 43

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 43

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

661 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