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.
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
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
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).
If the solutions have to be whole numbers, what you have is known as a Diophanitine equation. Here and here 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.
I mean, a Diophantine equation—sorry about the misspelling.
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.
(And for what it’s worth, I’ve figured out my own answer, with a little Googling on how to use Excel)
Daniel
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).
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((A1C1)+(B1D1))” 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
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