Circular Reference Override

[Bob]

Hello

I am in the process of setting up a template worksheet
used to calculate the total power used my mechanical
equipment in a building. There are three units which
manufacturers can publish mechanical equipment energy
consumption. Watts (W), Amps (A) or Horsepower (HP).
Using two conversion formulas one can convert amps and
horsepower over to watts without a problem.

What I would like to do is have a formula in each cell
(B2, C2 and D2 in the sheet below) to be prepared for a
future user to enter a value in to any of the cells as in
Case 1-3. In my opinion, this would involve a series of
if statement using logical expression (i.e. <, >, =
expressions).

A B C D
1 A HP W
2 Form Formula Formula Formula
3 Case 1 # Formula Formula
4 Case 2 Formula # Formula
5 Case 3 Formula Formula #

I have merrily plugged along and have been plagued by
circular references because the logical expressions are
all depended on what the other two values are. This would
be true only prior to the form being used. Once a user
would enter a value in any of the cells and overwrites
that formula it would break the circular reference loop.

I have tinkered with the iteration settings in Excel, but
I am getting a #VALUE error message and I believe that it
is occurring because I am using <,>, etc. operations
within my formulas so it would never converge regardless
of how many times it is calculated.

What I need is a way to tell excel not to worry about the
circular reference between the three formulas in column B,
C and D because one will be over written and will break
the error loop. Does anyone have any opinions? Perhaps I
could go about this in a different way.? Suggestions are
welcome.

Thanks for your insight!
-Bob

 

[hgrove]

Bob wrote...

I am in the process of setting up a template worksheet used to
calculate the total power used my mechanical equipment in a
building. There are three units which manufacturers can publish
mechanical equipment energy consumption. Watts (W), Amps
(A) or Horsepower (HP). Using two conversion formulas one can
convert amps and horsepower over to watts without a problem.

What I would like to do is have a formula in each cell (B2, C2
and D2 in the sheet below) to be prepared for a future user to
enter a value in to any of the cells as in Case 1-3. In my opinion,
this would involve a series of if statement using logical
expression (i.e. <, >, = expressions). ...
I have merrily plugged along and have been plagued by
circular references because the logical expressions are all
depended on what the other two values are. This would be true
only prior to the form being used. Once a user would enter a
value in any of the cells and overwrites that formula it would
break the circular reference loop.

...

This is something no spreadsheet could do well if at all. This is als
an example of when it's best to separate inputs from calculations
Create an entry range in one worksheet, and use NO FORMULAS in it. Jus
have clearly labelled entry cells for either watts, amps (you'r
assuming a common voltage for all circuits?) or horsepower. At most ad
another column with an IF formula comparing all entries for consistency
e.g.,

=IF(DEVSQ(watts,IF(ISNUMBER(amps),AmpsToWattsFormula),
IF(ISNUMBER(horsepower),HPToWattsFormula))>Tolerance,
"ERROR: multiple inconsistent entries","")

where watts, amps and horsepower would be references to the appropriat
cells in the same row in the entry range, and Tolerance would be
positive number expressing the point below which you'd be comfortabl
ignoring rounding error.

Then in a separate worksheet, create a summary/display range that pull
watts data for each entry in the entry range. The formulas woul
reference the 'validation' formula, checking whether it's a zero lengt
string or not, and if it's "", returning the maximum of the possibl
converted entries. E.g.,

=IF(validation="",MAX(watts,AmpsToWattsFormula,
HPToWattsFormula),#NUM!