Non circular references

**jammerculture** · 05-29-2010, 12:13 AM

I want to create a spreadsheet where all of the fields are related to one another by a formula and you can enter information in any of the fields and the program will fill in the rest. A simple example would be a metric to imperial conversion where you would have two fields and could enter a number in either field to get the other. Is this possible with excel? My attempts have given me circular relations.

**DonkeyOte** · 05-29-2010, 03:37 AM

Either you're looking at using VBA or you would keep the entry cells and calculation cells separate.

Generally speaking the latter is the much wiser choice esp. if not familiar with VBA.
In the context of the imperial/metric conversion this means you would have two cells for entering either metric/imperial value and a further two cells which calculate outputs pending which of the entry cells is populated.

In the most basic design terms a cell in XL can not contain both formula and constant at the same time - it's very much an either / or scenario.
(in other words you can not input a value into a cell and have a calculation in the same cell simultaneously)

**jammerculture** · 05-29-2010, 03:33 PM

Hey thanks for the reply. Is there another program that can do what I want? I am looking to create a tool to easily calculate various values for electrical calculations where different inputs are available. For example: ohms law is E=I*R, and sometimes you have E and R, sometimes R and I. This is a very basic example but there are some where there could be up to 10 variables. If not Excel then how?

Also if VBA could do this, I would be open to learning how. Could you point me in the right direction i.e. give me the terminology for what I am asking about so that I can ask the right questions in another forum?

**shg** · 05-29-2010, 03:51 PM

Maybe as attached for your simple example.

**jammerculture** · 05-29-2010, 05:58 PM

ok. Wow. That works but is way beyond my capabilities with excel. Can you point me in the right direction on how to learn how to do that?

Thanks

**shg** · 05-29-2010, 06:28 PM

=CHOOSE(COUNT($B4:$D4)+1, "underdefined", "underdefined", IF(B4<>"", B4, C4*D4), "overdefined")

The formula says if the number of inputs is 0 or 1, the problem is underdefined, and if it's 3, it's overdefined.

If exactly 2 inputs are provided, then if the voltage is specified, copy the voltage; if not, compute the voltage as the product of current and resistance.

All the formulas have exactly the same form, and differ only in the FALSE part of the IF function.

**jammerculture** · 05-29-2010, 07:39 PM

thanks for the help

what is this type of formula called in excel, so that I can search for more information about it

**shg** · 05-29-2010, 07:45 PM

It doesn't have a name, it's just a formula ...

**jammerculture** · 05-29-2010, 09:43 PM

right but if I wanted to search for information or tutorial about this type of fomula on the web, in this forum or in excel's help files, what sort of terminology would I use to get that information.