Need help composing a formula

[Hagar]

I have devised a formula, which gives me a phase angle relationship between
Watts and VARs (Electrical Power and Reactive Power). The result is a
numerical value of, for example 20 (degrees, phase angle) in cell "I7" (this
cell will be hidden)

Since the flow of power and reactive power is noted as IN or OUT (in text)
on the Excel test form, I need to come up with single cell, displaying the
resultant value for the following four variables:

If cell "D7" reads OUT and cell "F7" reads OUT, then the resultant value is
the same as cell "I7" (numerical value 20)

If Cell "D7" reads OUT and cell "F7" reads IN, then the resultant value is
"360-I7" (360 - 20), or 340.

If cell "D7" reads IN and cell "F7" reads OUT, then the resultant value is
"180-I7" (180-20), or 160.

If cell "D7" reads IN and cell "F7" reads IN, then the resultant value is
"180+I7" (180+20), or 200.

I don't know if text values (such as IN and OUT) can be incorporated into a
formula. If not, they could be substituted by + (for OUT) and - (for IN).

I have tried "=IF" statements, "=TRUE" statements, but somehow all I get is
garbage. Had a difficult enough time to come up with the "arctan" function
to get the correct phase angle in cell "I7".

Any help or suggestion would be very much appreciated.

Hagen M. Sahm
Relay Specialist
Sacramento, CA

 

[joeu2004]

Since the flow of power and reactive power is noted as IN or OUT (in text)
on the Excel test form, I need to come up with single cell, displaying the
resultant value for the following four variables:

If cell "D7" reads OUT and cell "F7" reads OUT, then the resultant value is
the same as cell "I7" (numerical value 20)
If Cell "D7" reads OUT and cell "F7" reads IN, then the resultant value is
"360-I7" (360 - 20), or 340.
If cell "D7" reads IN and cell "F7" reads OUT, then the resultant value is
"180-I7" (180-20), or 160.
If cell "D7" reads IN and cell "F7" reads IN, then the resultant value is
"180+I7" (180+20), or 200.

There are many ways to do this. The most intuitive might be:

if(D7="OUT", if(F7="OUT"), I7, 360-I7), if(F7="OUT", 180-I7, 180+I7))

More succinctly:

choose((D7="OUT") + 2*(F7="OUT") + 1, 180+I7, 360-I7, 180-I7, I7)

The choose indexes are: (IN,IN)=1, (OUT,IN)=2, (IN,OUT)=3 and
(OUT,OUT)=4.

 

[Bernard Liengme]

If you are SURE that D7 and F7 will always contain either OUT or IN then
=IF(D7="OUT",IF(F7="OUT",I7,360-I7),IF(F7="OUT",180-I7,180+I7))
If you are not sure, then
=IF(AND(OR(D7="IN",D7="OUT"),OR(F7="IN",F7="OUT")),IF(D7="OUT",IF(F7="OUT",I7,360-I7),IF(F7="OUT",180-I7,180+I7)),
"not defined")
Alternatively
=AND(D7="OUT",F7="OUT")*I7+AND(D7="OUT",F7="IN")*(360-I7)+AND(D7="IN",F7="OUT")*(180-I7)+AND(D7="IN",F7="IN")*(180+I7)
best wishes

 

[Don Guillett]

try expanding on this idea
=IF(AND(D7="out",F7="in"),1,IF(AND(D7="in",F7="out"),2,0))

 

[Aqib Rizvi]

Also try this.

=IF(AND(D7="OUT",F7="OUT"),I7,IF(AND(D7="OUT",F7="IN"),360-I7,IF(AND(D7="IN",F7="OUT"),180-I7,IF(AND(D7="IN",F7="IN"),180+I7,"Conditions
Not Met"))))

Best Regards
AQIB RIZVI

 

[Bernard Liengme]

The CHOOSE is very nice!

 

[Hagar]

Bernard,

Even though the multiple responses to my request were astonishingly quick
and the offered option varied, I found your option #2 to be the easiest to
implement.

Thank you very much for your very quick response.

Sincerely,
Hagen Sahm

 

[Hagar]

Thank you all for your most valuable input. I knew I was on the right track
with the "=IF" statement, but just couldn't get there by myself.

I have the formula working, thanks to all your replies.

Again, thank you all very much,

Hagen Sahm