complex (for mr anyway) if and statment

[jtcsrvbi]

i am working on a compass worksheet in excel. i enter in starting and ending Longitude and latitudes and it calculates distance. i have calculated the percent of 90 deg now i just need to put it in the right quadrant.

If Lat1 > 0 and if Lon1 >0 then Percent * 90 If Lat1 < 0 and If Lon1>0 then Percent * 90 +90

If Lat1 < 0 and if Lon1 <0 then Percent *90+180 If Lat1 > 0 and if Lon1 <0 then Percent *90+270








returned value


variables


Lat1
Lon1
Percent



thanks for taking a look!!

john

 

[Snakehips]

John,

Welcome to MrExcel.

You do not mention what happens if Lat1 or Lat2 = 0 ?

Given the conditions stated and with La1 in A, Lat2 in B2 and Percent in C2 then maybe....

Excel 2007
	F
2	270
Sheet3

Cell Formulas
Range	Formula
F2	=(C2*90) +(IF(AND(A2>0,B2>0),0,IF(AND(A2<0,B2<0),2,IF(A2<0,1,3)))*90)

Hope that helps.

 

[jtcsrvbi]

i am trying to digest this... i converted a2, b2 and c2 to my variables. i got a 508 error. i am using open office but near as i can tell they are the same. you use "," and they use ";" this might be my problem


thanks for your help...

 

[jtcsrvbi]

i am trying to digest this... i converted a2, b2 and c2 to my variables. i got a 508 error. i am using open office but near as i can tell they are the same. you use "," and they use ";" this might be my problem


thanks for your help...

I got it!!!!

=(Perc*90) +(IF(AND(Lat_val>0;Long_Val>0);0;(IF(AND(Lat_val<0;Long_Val>0);90;(IF(AND(Lat_val<0;Long_Val<0);180;(IF(AND(Lat_val>0;Long_Val<0);270))))))))

works perfect!!! thanks Tony for help with the syntax!!!

 

[Snakehips]

Glad I was able to give you a nudge in the right direction.
I did not appreciate that you were using named ranges and that your separator was semi-colon.

It really doesn't matter but can your formula not reduce to...

=(Perc*90) +(IF(AND(Lat_val>0;Long_Val>0);0;IF(AND(Lat_val<0;Long_Val>0);90;IF(AND(Lat_val<0;Long_Val<0);180;270))))

 

[jtcsrvbi]

i see... i don't need to do the last check because if the other 3 failed it must be 4... thanks again... i really learned a lot!!


john