Answer's a little more complicated than regular formulas, but what do I need to do?

[Jordan Smith]

I have three columns: "Number 1," "Number 2," and "Difference" (which
is Num 1 - Num2).

In a given row, I want to be able to enter any 2 of the fields and have
Excel calculate the 3rd one.

I.e.: (=x are the numbers excel calculates. The other numbers I type
in)
Num1 Num2 Diff
4 2 =2
=2 1 1
3 =2 1


How can this be done?

Thanks,
Jordan

 

[John James]

If you can live with circular references until the values are entered:

Cell A2:
=B2+C2

Cell B2:
=A2+C2

Cell C2:
=A2-B2

Copy formulae down


Otherwise separate the input area (A2:C100) and the output area
(D2100)

Cell D2:
=IF(COUNTA(A2:C2)<2,"",IF(A2="",B2+C2,A2))

Cell E2:
=IF(COUNTA(A2:C2)<2,"",IF(B2="",A2+C2,B2))

Cell F2:
=IF(COUNTA(A2:C2)<2,"",IF(C2="",A2-B2,C2))

Copy formulae down

 

[Guest]

Hi Jordan
I don't really know why you would want to do this, but here is one solution.

If the column headers are in A1,B1, and C1 create 3 more columns in say
E1,F1 and G1.

Now, in cell E2 put the formula =IF(A2="",B2+C2,A2) and copy down as far as
you need.
In Cell F2 put the formula =IF(B2="",A2-C2,B2) and do the same.
Finall, in cell G2 put the formula=IF(C2="",A2-B2,C2) and also copy down as
far as needed.

As you fill in the data in the original cells, the new columns will populate
the required answers.

When completed you can hide the original columns.
This is a fairly ugly solution, but will work. Other option is to use VB

HTH
Michael.

In cell

 

[Jordan Smith]

Thanks John.

I don't really want the circular references because I want to make it
possible to change the numbers. Seperating out the input from output
looks like what I will have to do. (I wasn't aware of the counta
function which will help also.

Thanks again.

Michael: I know that my example may raise eyebrows. I am working on a
more complicated spreadsheet and used this example to get an answer to
the problem without bogging everyone down in understanding my "final"
goal Thanks for your answer though. I'm going to try and use what
you guys have given me tomorrow. It's just about 3am here, haha.