Circular Reference Override

  • Thread starter Thread starter Bob
  • Start date Start date
B

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
 
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.
Click to expand...

Turn on Iteration in the excel options.
 
Jonathan,

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

-Bob
 
Anonymous wrote...
Thanks for your tip, but the circular references are due to <,>,=
operations so the number of iterations would not help,right?
Click to expand...
...

For the shear unmitigated heck of it, why not try Jonathan's suggestio
before second guessing it
 
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
 
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
 
Thanks for your tip, but the circular references are due
to <,>,= operations so the number of iterations would not
help,right?
Click to expand...

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.
 
Back
Top