Circular Reference Override

[Bob]

Hello

I am in the process of setting up a template form used to
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.

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.

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. I realize why they are popping up,
but I was wondering if anyone knew of a way to over ride
them because as soon as the user enters a number into one
of the three cells it would terminate the circular
reference. Any insight would be much appreciated!!
-Bob

 

[Jonathan Rynd]

I realize why they are popping up,
but I was wondering if anyone knew of a way to over ride
them because as soon as the user enters a number into one
of the three cells it would terminate the circular
reference.

Turn on Iteration in the excel options.

 

[Guest]

Jonathan,

Thanks for your tip, but the circular references are due
to <,>,= operations so the number of iterations would not
help,right?

-Bob

 

[hgrove]

Anonymous wrote...

Thanks for your tip, but the circular references are due to <,>,=
operations so the number of iterations would not help,right?

...

For the shear unmitigated heck of it, why not try Jonathan's suggestio
before second guessing it

 

[Guest]

I appreciated Jonathan's suggestion and in fact did test
it prior to replying to his posting. The fact of the
matter is that it did not work. I am getting a #VALUE
error message and I believe that it is occurring because I
am using <,>, etc. operations within my formula.
Therefore, I decided to reply to Jonathan's posting to try
and get his or others opinions on why the iterations
settings in excel did not solve my problem. So if anyone
has constructive comments to my posting I would appreciate
hearing from you.

Regards,
-Bob

 

[Bernard Liengme]

Why not have the use enter in A2 the numeric value and in B2 the unit
(W,A,HP) then compute the three amonuts in C2, D2 and E2
Example
A2: 12
B2: A (this could have a validation check or a drop-down list)
C2: =IF(B2="A",A2,IF(B2="W",B2*watt-to-amp-factor,B2*hp-to-amp-factor)
D2: =IF(B2="W",A2,IF(B2="A",B2/watt-to-amp-factor,B2*hp-to-WATT-factor)
e2: =IF(B2="HP",A2,IF(B2="A",B2*amp-to-hp-factor,B2/hp-to-watt-factor)

best wishes
Bernard

 

[Jonathan Rynd]

Thanks for your tip, but the circular references are due
to <,>,= operations so the number of iterations would not
help,right?

In order for iteration to work, you have to have good values in the cells
at one point. So it may be necessary to jumpstart the calculation by
entering constants, and then recalculating, and then replacing the
constants with formulas.

All operations are equivalent in the eyes of Excel. Nothing special about
the comparison operators.