Making Excel Automatically Calculate 2 Out of 4 Values

[SherlockOhms]

So, I'm a creating a spreadsheet which receives a number of inputs from the user and then does various calculations with these values.
The user need only input 2 out of the 4 inputs below and as you can see, with 2 values, the other 2 can be calculated. How do I make it so that excel does this automatically?

So, R = C_P - C_V (1)
Gamma = C_P/C_V(2)
C_V = R/Gamma - 1 (3)
C_P = R + C_V (4)

I've inputted the above formulas into the relevant cells for R, Gamma, C_V and C_P (i.e. (1) into R's cell, (2) into Gamma's cell, (3) into C_V's cell and (4) into C_P's cell) but when I go and input R and Gamma, C_V and C_P aren't automatically calculated. I actually keep getting some error message about a circular reference.

 

[Mark44]

You're either going to have to have some pretty involved formulas or you'll need to write some Excel Visual Basic for Applications (VBA) code to do this. I haven't thought about this very deeply, but here are my initial thoughts. Let's say you have four cells across the top for your input values.

On another row farther down, you have four more cells for the output values, preferably with some sort of labels next to them so the user can tell which is which.

The cells lower in the table would have formulas that either copy the value that's in the corresponding cell in the first row (assuming the user entered a value) or compute a value using two of the values in the first row.

As an example, if the user entered values for the first two cells in row 1, these values would be copied directly to the first two cells in the lower row. The other two values in the lower row would be calculated from the two entered values in the first row.

Your formulas or VBA would need to check which two cells in the first row are empty, and there are 6 ways this can happen; i.e. 1st & 2nd, 1st & 3rd, 1st & 4th, 2nd & 3rd, 2nd & 4th, 3rd & 4th.

I'm assuming that given any two of the values, the other two can be calculated with no other information needed. That might or might not be the case - I haven't done any checking.
This link applies to Excel 2010 VBA:
http://msdn.microsoft.com/en-us/library/ee814737(v=office.14).aspx

 

[SherlockOhms]

I've never actually written any VBA. Could I implement this in excel just using a few IF statements and functions?

 

[Mark44]

Yes, I believe so.

Edit:
As a proof of concept, I put together a quick Excel sheet that uses the formula for power (watts), current (amps), and voltage (volts).

Across the top I have three input cells in which the user puts any two of the three values.

A few rows down I have three more cells for the computed values of power, current, and voltage. The formula for power looks like this:

=if(isblank(a2), b2*c2, a2)

The formula for current looks like this:

=if(isblank(b2), a2/c2, b2)

The formula for voltage looks like this:

=if(isblank(c2), a2/b2, c2)

 

[TheoMcCloskey]

Maybe I'm missing something, but, if the user wants Cv and Cp from inputs R and Gamma, why not just use the last two equations (3) and (4).

Equation (4) is just a re-write of equation (1). Also, Equation (3) is just a re-write of equation (2) using equation (1) (I believe equation 3 is really Cv =R/(Gamma-1) )

Use two blank cells to hold inputs R and Gamma

Construct formulaes in two other cells to calculate Cv and Cp

Cv = R / (Gamma - 1)
Cp = R + Cv

 

[Mark44]

Maybe I'm missing something, but, if the user wants Cv and Cp from inputs R and Gamma, why not just use the last two equations (3) and (4).

Equation (4) is just a re-write of equation (1). Also, Equation (3) is just a re-write of equation (2) using equation (1) (I believe equation 3 is really Cv =R/(Gamma-1) )

Use two blank cells to hold inputs R and Gamma

Construct formulaes in two other cells to calculate Cv and Cp

Cv = R / (Gamma - 1)
Cp = R + Cv

I haven't played with these formulas at all, but the idea was that a user would enter any two of the quantities, not just R and Gamma.

 

[Ibix]

A slight generalisation of Mark's method would be to put the power, current and voltage in cells B2, C2 and D2 respectively. In cell A5 you write

=if(and(isblank(b2),not(isblank(c2)),not(isblank(d2))),1,0)

which returns 1 if and only if the user has filled in c2 and d2 but not b2. You put the other two permutations in A6 and A7. Then you populate cells B5:D7 with if statements that compute the correct answer if the corresponding cell in column A is 1, and return zero otherwise. So, for example, cell B5 would contain:

=if($a5=1,c2*d2,0)

You can get your final answers with sum(b5:b7), sum(c5:c7) and sum(d5:d7). If the user hasn't filled in two values, this sum is zero because all of the cells A5:A7 are zero. If the user has filled in two values, exactly one of the cells A5:A7 will be 1, and the corresponding cells in columns B, C and D will be non-zero. The sum of b5:b7 will be the correct answer plus zero plus zero.

I think this is an improvement over Mark's method (although it's overkill for Mark's example and arguably for yours) because it largely separates the "which maths should I use?" from actually doing the maths. The decision is in column A; the work is in columns B:D; a clever trick is used to gather up the final result.

Does this all make sense?

 

[SherlockOhms]

Thanks for the reply. I'll read through that and get back to you later on when I get a chance.