# Algebra: solving for two variables with Excel **URL:** \ **Category:** Factual Questions\ **Created:** [October 8, 2005, 6:24pm UTC](https://boards.straightdope.com/t/algebra-solving-for-two-variables-with-excel/325331 "2005-10-08T18:24:37Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Mr.Blue\_Sky](https://avatars.discourse-cdn.com/v4/letter/m/d26b3c/32.png) [@Mr.Blue\_Sky](https://boards.straightdope.com/u/Mr.Blue_Sky)\ **Post date:** [October 8, 2005, 6:24pm UTC](https://boards.straightdope.com/t/algebra-solving-for-two-variables-with-excel/325331/1 "2005-10-08T18:24:37Z")
No, this isn’t a homework qeustion (I haven’t had homework for 25 years!). Can you write a formula for Excel that will solve a two-variable equation? Take this equation: 2x + 19y = 21,544 where x & y are whole numbers. ---
**Author:** ![Left\_Hand\_of\_Dorkness](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/left_hand_of_dorkness/32/7156_2.png) [@Left\_Hand\_of\_Dorkness](https://boards.straightdope.com/u/Left_Hand_of_Dorkness)\ **Post date:** [October 8, 2005, 6:30pm UTC](https://boards.straightdope.com/t/algebra-solving-for-two-variables-with-excel/325331/2 "2005-10-08T18:30:20Z")
Wow, this is almost exactly like a question I have–which is, how do you get Excel to solve for a single variable? Mine **is** for a homework question, but only so that I can have a tool to solve the (extremely open-ended and wonderfully frustrating) puzzle that the teacher has given us, a puzzle which she’s not solved herself; I am already using Excel to try to find a solution, but being able to do my algebraic equations automatically instead of by hand would greatly speed things up. (And if I wanted to cheat, I’d just look at an online answer, whose link I’ve already discovered) Daniel ---
**Author:** ![Left\_Hand\_of\_Dorkness](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/left_hand_of_dorkness/32/7156_2.png) [@Left\_Hand\_of\_Dorkness](https://boards.straightdope.com/u/Left_Hand_of_Dorkness)\ **Post date:** [October 8, 2005, 6:32pm UTC](https://boards.straightdope.com/t/algebra-solving-for-two-variables-with-excel/325331/3 "2005-10-08T18:32:50Z")
I should add, **Mr. Blue Sky** , that your sample equation will have an infinite number of answers, won’t it? For example, x=10772 and y=0, or x=10753 and y=2. Are you looking for line graph answers? Daniel ---
**Author:** ![F.U.Shakespeare](https://avatars.discourse-cdn.com/v4/letter/f/aca169/32.png) [@F.U.Shakespeare](https://boards.straightdope.com/u/F.U.Shakespeare)\ **Post date:** [October 8, 2005, 6:40pm UTC](https://boards.straightdope.com/t/algebra-solving-for-two-variables-with-excel/325331/4 "2005-10-08T18:40:13Z")
Could you clarify something for me, **Mr. Blue Sky**? You know you can’t completely solve the equation (i.e., find what numbers x and y are equal to) since you have two unknowns and only one equation. You _can_ solve the equation for either variable in terms of the other variable: x = (21544 - 19y)/2 y = (21544 - 2x)/19 Is this what you mean? If you instead have two equations and two unknowns, you can use Excel to implement Cramer’s Rule. I can describe that for you if that’s what you’re after. (On preview, I se that **LHOD** has raised this point). ---
**Author:** ![Thudlow\_Boink](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/thudlow_boink/32/320_2.png) [@Thudlow\_Boink](https://boards.straightdope.com/u/Thudlow_Boink)\ **Post date:** [October 8, 2005, 6:46pm UTC](https://boards.straightdope.com/t/algebra-solving-for-two-variables-with-excel/325331/5 "2005-10-08T18:46:43Z")
If the solutions have to be whole numbers, what you have is known as a Diophanitine equation. [Here](http://www.alpertron.com.ar/QUAD.HTM) and [here](http://www.math.uwaterloo.ca/~snburris/htdocs/linear.html) are a couple of web sites I found that will solve such things for you—typically you’ll have infinitely many solutions (if you have any at all) and you’ll get a formula where you can get different particular solutions by plugging in different values of a parameter. (Excel could help you get a list of these particular solutions once you have the general formula). One quick-and-dirty thing you could do with Excel is to solve your equation for one of the variables (as **F. U. Shakespeare** has done), use this in Excel to give you a whole bunch of solutions, and go through and pick out the whole-number solutions. ---
**Author:** ![Thudlow\_Boink](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/thudlow_boink/32/320_2.png) [@Thudlow\_Boink](https://boards.straightdope.com/u/Thudlow_Boink)\ **Post date:** [October 8, 2005, 6:47pm UTC](https://boards.straightdope.com/t/algebra-solving-for-two-variables-with-excel/325331/6 "2005-10-08T18:47:47Z")
I mean, a **Diophantine** equation—sorry about the misspelling. ---
**Author:** ![Iacob\_Matthew](https://avatars.discourse-cdn.com/v4/letter/i/bbe5ce/32.png) [@Iacob\_Matthew](https://boards.straightdope.com/u/Iacob_Matthew)\ **Post date:** [October 8, 2005, 6:58pm UTC](https://boards.straightdope.com/t/algebra-solving-for-two-variables-with-excel/325331/7 "2005-10-08T18:58:13Z")
I’ve seen excel do some complicated things, so I don’t doubt that it can. However, I don’t know how to program in excel, because I’ve never needed to use it. I can give two methods for how you might solve it, though. If it’s just your equation, then there are infinite solutions. 19y=21,544 - 2x y=2/19 \* (10772-x) this gives you the relation your Y’s should have for any X. First, if you have two or more equations which would have the same X & Y value, you can subtract the two equations. As in, 2x + 19y = 21,544 & 4x + 50y = 50,000 New Equation - twice your equation gets (4x- (2)2x) + (50y - (2)19y) = (50,000 - (2)21,544) 0x + 2y = 7,912 y=(7,912)/2, plug in and solve for x On Preview, **Thudlow** ’s second link looks like Archimede’s equation- easy in process, but it’s a bit of a pain to explain. Google it and see if you can see enough examples to figure it out. ---
**Author:** ![Left\_Hand\_of\_Dorkness](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/left_hand_of_dorkness/32/7156_2.png) [@Left\_Hand\_of\_Dorkness](https://boards.straightdope.com/u/Left_Hand_of_Dorkness)\ **Post date:** [October 8, 2005, 7:29pm UTC](https://boards.straightdope.com/t/algebra-solving-for-two-variables-with-excel/325331/8 "2005-10-08T19:29:14Z")
(And for what it’s worth, I’ve figured out my own answer, with a little Googling on how to use Excel) Daniel ---
**Author:** ![Mr.Blue\_Sky](https://avatars.discourse-cdn.com/v4/letter/m/d26b3c/32.png) [@Mr.Blue\_Sky](https://boards.straightdope.com/u/Mr.Blue_Sky)\ **Post date:** [October 8, 2005, 7:32pm UTC](https://boards.straightdope.com/t/algebra-solving-for-two-variables-with-excel/325331/9 "2005-10-08T19:32:38Z")
> [@F. U. Shakespeare](#): > > Could you clarify something for me, **Mr. Blue Sky**? > > You know you can’t completely solve the equation (i.e., find what numbers x and y are equal to) since you have two unknowns and only one equation. > > You _can_ solve the equation for either variable in terms of the other variable: > > x = (21544 - 19y)/2 > > y = (21544 - 2x)/19 > > Is this what you mean? > > If you instead have two equations and two unknowns, you can use Excel to implement Cramer’s Rule. I can describe that for you if that’s what you’re after. > > (On preview, I se that **LHOD** has raised this point). I could solve it that way well enough, but I was looking for a way to get an exact numeric answer. What I need this for is for the work I do (hospital insurance billing). If we bill two different room rates and what the insurance company pays is not what we’re supposed to get, I have to figure out what rates they used to determine the payment. I have a fee schedule of the various rates they _could have_ used, but I’d be sitting there plugging in a dozen different numbers until I got the right combination. If I could plug this into a formula, it would save me some time (of course, I could always call the insurance company, but where’s the fun in that? :)) The answer to the above equation is x=1386 and y=988: 2 x 1386 = 2772 19 x 988 = 18772 2772 + 18772 = 21544 ---
**Author:** ![AHarris](https://avatars.discourse-cdn.com/v4/letter/a/bcef8e/32.png) [@AHarris](https://boards.straightdope.com/u/AHarris)\ **Post date:** [October 8, 2005, 8:12pm UTC](https://boards.straightdope.com/t/algebra-solving-for-two-variables-with-excel/325331/10 "2005-10-08T20:12:58Z")
> [@Mr. Blue Sky](#): > > I could solve it that way well enough, but I was looking for a way to get an exact numeric answer. What I need this for is for the work I do (hospital insurance billing). > > If we bill two different room rates and what the insurance company pays is not what we’re supposed to get, I have to figure out what rates they used to determine the payment. I have a fee schedule of the various rates they _could have_ used, but I’d be sitting there plugging in a dozen different numbers until I got the right combination. If I could plug this into a formula, it would save me some time (of course, I could always call the insurance company, but where’s the fun in that? :)) > > The answer to the above equation is x=1386 and y=988: > > 2 x 1386 = 2772 > 19 x 988 = 18772 > 2772 + 18772 = 21544 It is impossible to find a discrete solution to a 2 variable equation without knowing: a) another equation with both variables that is not a multiple of the first or b) a single value for one of the variables. Here we have two variables, one of which you have a list of possible values for, so you can determine the value of one variable for each value of the other variable. For example to find the possible values for x in Excel you would need to do something like: A1: y B1: =(21544-19\*A1)/2 B1 will give you x (whole number as long as y is even) Simply copy the formula down column B and input the appropriate y values into column A (according to the schedule). ---
**Author:** ![Left\_Hand\_of\_Dorkness](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/left_hand_of_dorkness/32/7156_2.png) [@Left\_Hand\_of\_Dorkness](https://boards.straightdope.com/u/Left_Hand_of_Dorkness)\ **Post date:** [October 8, 2005, 8:33pm UTC](https://boards.straightdope.com/t/algebra-solving-for-two-variables-with-excel/325331/11 "2005-10-08T20:33:16Z")
> [@Mr. Blue Sky](#): > > IThe answer to the above equation is x=1386 and y=988: > > 2 x 1386 = 2772 > 19 x 988 = 18772 > 2772 + 18772 = 21544 Again, this is one of an infinite number of possible solutions to the above equation. If the insurance company will use the same rate for at least two different billings, then you can solve the problem. Or, if there are a set of common rates they could use, you could set up a table like this: 1288…884 1185…786 1044…489 etc. That’s your column A and B, with the different possible rates. Columns C and D are where you put the different number of days at each rate. For the example, C1 would equal 2 and D1 would equal 19. You’d then copy these values down to all the other cells. Column E is an equation: “=sum((A1_C1)+(B1_D1))” This will give you the total billing if the insurance company used the rates in the first row. Copy this formula down to all the other rows. When you get a new bill, just fill in columns C and D, leaving column E the same. Look for the bill’s total in column E; when you find it, you’ll know which set of rates the company used. Does this answer your question? Or am I completely out in left field, as is possible? Daniel ---
**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\ **Post date:** [October 8, 2005, 9:00pm UTC](https://boards.straightdope.com/t/algebra-solving-for-two-variables-with-excel/325331/12 "2005-10-08T21:00:37Z")
There may well be only one answer if you’re restricted to positive integer solutions. For instance, x + y = 2 has exactly one solution over that set. ---
**Author:** ![Left\_Hand\_of\_Dorkness](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/left_hand_of_dorkness/32/7156_2.png) [@Left\_Hand\_of\_Dorkness](https://boards.straightdope.com/u/Left_Hand_of_Dorkness)\ **Post date:** [October 9, 2005, 2:01am UTC](https://boards.straightdope.com/t/algebra-solving-for-two-variables-with-excel/325331/13 "2005-10-09T02:01:01Z")
> [@ultrafilter](#): > > There may well be only one answer if you’re restricted to positive integer solutions. For instance, x + y = 2 has exactly one solution over that set. True, but I believe that only works when all variables equal one, and I suspect that hospital stays cost more than a buck a night. Daniel