 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2006Need help composing a formula
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 > 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 [quoted text clipped - 8 lines] > 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. 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 The CHOOSE is very nice!  SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email >> Since the flow of power and reactive power is noted as IN or OUT (in >> text) [quoted text clipped - 24 lines] > The choose indexes are: (IN,IN)=1, (OUT,IN)=2, (IN,OUT)=3 and > (OUT,OUT)=4. 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 SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email >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 [quoted text clipped - 30 lines] > Relay Specialist > Sacramento, CA 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 > 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)) [quoted text clipped - 38 lines] >> Relay Specialist >> Sacramento, CA try expanding on this idea =IF(AND(D7="out",F7="in"),1,IF(AND(D7="in",F7="out"),2,0)) SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com >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 [quoted text clipped - 30 lines] > Relay Specialist > Sacramento, CA 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
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.