Using Solver for 3 variables

Short but useless answer -- Yes, you should be able to use Solver for this. I'm not sure what you have tried, but I would expect:

1) Choose cells for your C values and enter your best "guesses" for these.
2) Calculate TC and AC in two cells.
3) Call Solver and tell it to set TC to a value of [known value of TC] by changing C1, C2, and C3 subject to the constraint that AC=[known value of AC].

Note that a system of equations with 2 equations and 3 unknowns will be underspecified -- there will be many possible solutions. Solver will find one solution and will likely never find the same solution twice. As long as you are satisfied with "any valid solution," then this should work. If you need Solver to find a specific solution among the many possible solutions, you will need to add additional constraints so that Solver knows which of the many solutions is the right solution.


If you are not required to use Solver, I note that your equations are both linear, so you can also solve this using strategies for solving systems of linear equations (https://www.mathsisfun.com/algebra/s...equations.html
https://www.mathsisfun.com/algebra/s...-matrices.html )
You will need to choose a (random?) value for one of the C's then you can find the other two C's using those strategies (remember that Excel has build in functions to work with matrices, which often makes the matrix algebra based solutions easiest to program into the spreadsheet).

quick and dirty.
You can add restrictions to the limits of c1:c3 ...
see MrShorty "If you need Solver to find a specific solution among the many possible solutions, you will need to add additional constraints so that Solver knows which of the many solutions is the right solution."

Originally Posted by sjjr

So, I have some data - Total Cost of all Houses (TC), Average Cost of Houses (AC), Total No of Houses (Type1-T1), Total No of Houses (Type2-T2), Total no of Houses (Type3-T3)
my equations are: TC= C1*T1 + C2*T2 + C3*T3 and AC = C1*T1 + C2*T2 + C3*T3 / (T1+T2+T3)

I believe there are some things wrong with your formulas.

First, the formula for AC looks like (and should be) a weighted average. To that end, it should be:

AC = (C1*T1 + C2*T2 + C3*T3) / (T1+T2+T3)

Second, substituting TC= C1*T1 + C2*T2 + C3*T3, the formula becomes:

AC = TC / (T1+T2+T3)

This is confirmed by the following.

home cost 1.jpg
Formula:
F4: =A4/SUM(B4:D4)
Copy F4 into F5

-----

So, there is only one equation with 3 variables.

And C1, C2 and C3 can be a randomly-selected set of numbers that satisfy the equation for TC.

This is demonstrated by the following.

home cost 2.jpg
Formulas:
G4: =IF(B4=0,0,$A4*L4/$O4/B4)
H4: =IF(C4=0,0,$A4*M4/$O4/C4)
I4: =IF(D4=0,0,$A4*N4/$O4/D4)
J4: =SUMPRODUCT(B4:D4,G4:I4)
K4: =J4/SUM(B4:D4)
L4: =IF(B4=0,0,RAND())
M4: =IF(C4=0,0,RAND())
N4: =IF(D4=0,0,RAND())
O4: =SUM(L4:N4)
Copy G4:O4 into G5:O4

L4/O4 (w1/wT) is the percentage of TC allocated to T1*C1. Thus, C1 = TC*w1/wT/T1.

Similarly for C2 and C3.

Open the attached Excel file, and press f9 repeatedly.

-----

Bottom line: For something meaningful, I suspect that you need more information.

-----

You've put a question mark on that. I don't know if you intend that to be a question or not. Obviously, we on this side of the internet, cannot know if you are required to find values for the Cs that go with each data point, or if you are required to find a single set of Cs that applies to all data points at the same time.

For the latter problem (find one set of Cs that "best fits" the T and TC (and/or AC) data), that sounds like a regression problem. You can solve regression problems using Solver, if you like (for non-linear regression problems in Excel, you must use Solver). In this case, the regression would be linear, in which case I find the LINEST() function is usually easier to setup and implement. Assuming you have more than the two data points that you mention in the OP, =LINEST(TC_data,T_data,FALSE) will return a horizontal array of C values that best fits the overall data set.

Of course, I am just guessing at what you mean by "they can't be different in each equation?" If I have guessed wrong, help us understand what you need to do (as much detail about algorithms and analysis goals and such), and we should be able to help program that into the spreadsheet.

it remains quick and especially dirty.
It depends how you define your Goal in E10.
There is no exact solution. (blue cells)

Originally Posted by sjjr

my equations are: TC= C1*T1 + C2*T2 + C3*T3
[...]
I have more than one set of values so:
TC1= $573,495, T1 = 530, T2 = 130, T3 = 3, AC= $865
TC2= $634,437, T1 = 461, T2 = 10, T3=0 AC= $1,347

Originally Posted by sjjr

the costs are constant so would have to be the same in each equation, i.e. a single set of Cs that fits all equations

If that were true, we could solve for c1 and c2 algebraically, choosing c3 somewhat arbitrarily (demonstrated below).

But that cannot be true, assuming that costs are non-negative, because with tc2 > tc1, you have fewer t1 and fewer t2 houses costing more than a greater number of t1 and t2 houses respectively plus some number of t3 houses.

It seems likely that total costs tc1 and tc2 represent two sampling of homes, and the average costs c1, c2 and c3 vary for each sampling of t1, t2 and t3 homes.

-----

The following demonstrates the algebraic solution, assuming tc2 < tc1.

home cost 3.jpg
Formulas for c1 and c2 (c3 is chosen arbitrarily):
G4: =(B5 - H4*D5) / C5
H4: =(C5*B4 - I4*E4*C5 - B5*C4) / (D4*C5 - D5*C4)
Other Formulas:
F4: =SUMPRODUCT(C4:E4, G4:I4)
F5: =SUMPRODUCT(C5:E5, G4:I4)


For valid tc2 (434437) < tc1, c3 can be any value between 0.001 and 24677.896.

For invalid tc2 (634437) > tc1, c3 must be no greater than -51967.079 (invalid). Otherwise, c2 is negative (invalid).

-----

How many equations would be needed so that only 1 possible set of C values would work?

At least 3.

Here's a quick example I put together with your two points and a random third point. The values are probably not realistic but it shows how to use LINEST() for this kind of problem.

I would note that, for three points, the internal algebra is the same as what curiouscat408 is showing for two points. If this is something you need to be proficient in, I recommend you go back and review your notes from algebra class (here's a tutorial if you did not keep your algebra class notes: https://www.purplemath.com/modules/systlin1.htm ) on solving systems of equations. The better you understand the algebra of this problem, the better equipped you will be to solve it as you encounter other examples.