complex (for mr anyway) if and statment

jtcsrvbi

New Member
Joined
Sep 18, 2014
Messages
4
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
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....

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">270</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=(<font color="Blue">C2*90</font>) +(<font color="Blue">IF(<font color="Red">AND(<font color="Green">A2>0,B2>0</font>),0,IF(<font color="Green">AND(<font color="Purple">A2<0,B2<0</font>),2,IF(<font color="Purple">A2<0,1,3</font>)</font>)</font>)*90</font>)</td></tr></tbody></table></td></tr></table><br />

Hope that helps.
 

jtcsrvbi

New Member
Joined
Sep 18, 2014
Messages
4
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

New Member
Joined
Sep 18, 2014
Messages
4
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!!!!:ROFLMAO:

=(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

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Sep 18, 2014
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,368
Messages
5,528,272
Members
409,813
Latest member
robyrux

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top