1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

IF functions on excel

Discussion in 'Business Applications' started by LauraPearson, Feb 10, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. LauraPearson

    LauraPearson Thread Starter

    Joined:
    Feb 10, 2010
    Messages:
    3
    Hi,

    I'm trying to calculate angles using Tan on excel based on data that is plotted on an x and y co-ordinate graph. I've produced a formula that means I can calculate the angle whereever the co-ordinates fall. I've tried this formula in excel and first it said I have too many arguments, I thnk you're only allowed 3 IF's? And then when I edited it, that it contains and error.

    =IF((AND(xcell>100,ycell>100), ((180-45)-(ABS(360-(DEGREES(ATAN(y-100/x-100)))))))),IF(AND(xcell>100,ycell<100),((180-45)-(ABS(DEGREES(ATAN(y-100/x-100))))))),IF(AND(xcell<100,ycell<100),((180-45)-(ABS(180-(DEGREES(ATAN(y-100/x100)))))))),IF((AND(xcell<100,ycell>100),((180-45)-(ABS(180+(DEGREES(ATAN(y-100/x-100)))))))))

    and when edited...

    IF(AND(G12>100,H12>100),(180-45)-(ABS(360-(DEGREES(ATAN((H12-100)/(G12-100)))))),IF(AND(G12>100,H12<100),(180-45)-(ABS(DEGREES(ATAN((H12-100)/(G12-100)))))),(IF(AND(G12<100,H12<100),((180-45)-(ABS(180-(DEGREES(ATAN((H12-100)/(G12-100)))))),(AND(G12<100,H12>100),((180-45)-(ABS(180+(DEGREES(ATAN((H12-100)/(G12-100))))))))))))

    Is there anything you can see in the formula that could be causing the problem?

    Thanks guys.:)
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,971
    First Name:
    Wayne
    what version of Excel
    You should be able to nest 7 IFs on 2003 - i think more on 2007

    if we could have a sample - dummy data - so that we can see the values....
    meantime I'll drop in excel 2003 on a different PC
     
  3. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,971
    First Name:
    Wayne
    OK - you have 4 arguments on the last IF
    see the four commas on the code broken down to seperate lines

    also not sure of the brackets

    Code:
    IF(
    
    AND(G12>100,H12>100)
    ,
    
    (180-45)-(ABS(360-(DEGREES(ATAN((H12-100)/(G12-100))))))
    ,
    	IF
    		(AND(G12>100,H12<100)
    		,
    		(180-45)-(ABS(DEGREES(ATAN((H12-100)/(G12-100))))))
    		,
    
    			(IF
    				(AND(G12<100,H12<100)
    				,
    				((180-45)-(ABS(180-(DEGREES(ATAN((H12-100)/(G12-100))))))
    				,
    				(AND(G12<100,H12>100)
    				,
    
    				((180-45)-(ABS(180+(DEGREES(ATAN((H12-100)/(G12-100))))))))))))
    And looking in the same way at the first formula - you dont seem to have a False condition for the Last IF

    Code:
    =IF
    (
    
    (AND(xcell>100,ycell>100)
    , 
    
    ((180-45)-(ABS(360-(DEGREES(ATAN(y-100/x-100))))))))
    ,
    	IF
    	(AND(xcell>100,ycell<100)
    	,
    	((180-45)-(ABS(DEGREES(ATAN(y-100/x-100)))))))
    	,
    		IF
    		(AND(xcell<100,ycell<100)
    		,
    		((180-45)-(ABS(180-(DEGREES(ATAN(y-100/x100))))))))
    		,
    			IF
    			((AND(xcell<100,ycell>100)
    			,
    			((180-45)-(ABS(180+(DEGREES(ATAN(y-100/x-100)))))))))
    
     
  4. LauraPearson

    LauraPearson Thread Starter

    Joined:
    Feb 10, 2010
    Messages:
    3
    Hi, thanks for replying!

    Firstly, I have the latest student excell from 2007.

    Here's some sample data;
    X dist Y dist46-980-11-138-9117-655283-218-27564568-99419401820-37-28-127-76-152-102389-65-12-152-112

    On the first one there's 4 arguments because i read somewhere that four conditions should only require 3 'IF's as if it is not the first 3, the solution must be in the last logic.

    And on the second breakdown, there is no false condition, because following the same rationale, if it doesn't fit the first 3 IFs it is in the 4th thus I want it to compute the 4th logic. This is where my problem seems to fall. And also, I'll check the brackets.

    Thanks.
     
  5. LauraPearson

    LauraPearson Thread Starter

    Joined:
    Feb 10, 2010
    Messages:
    3
    X distance Y distance
    -152 -112
    -65 -12
    38 9
    -152 -102
    -127 -76
    -37 -28

    Hopefully this will work...
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,971
    First Name:
    Wayne
    sort of
    But your not quite doing that - I dont have 2007 - but the IF function requires 2 arguments seperated by a common

    SO
    IF
    Condtion ,
    TRUE ,
    FALSE ,

    so looking at your set up

    AND REPLACING with Condition TRUE FALSE

    Code:
    IF(
    
    [b][color=red]CONDITION[/color][/b]
    ,
    
    = [b][color=red]TRUE[/color][/b]
    '	
    = [b][color=red]FALSE [/color][/b]   -  now for that you ADD an IF
    		= [b][color=red]CONDITION[/color][/b]
    		,
    		= [b][color=red]TRUE[/color][/b]
    		,
    		= [b][color=red]FALSE [/color][/b] AND you add another IF (IF
    				= [b][color=red]CONDITION[/color][/b]
    				,
    				= [b][color=red]TRUE[/color][/b]
    				,
    				
                                   = [b][color=red]FALSE[/color][/b]  - but this looks like a condition  (AND(G12<100,H12>100)
    				,
    
    				= [b][color=red]Shoud NOT be a 4th comma !!![/color][/b]   ((180-45)-(ABS(180+(DEGREES(ATAN((H12-100)/(G12-100))))))))))))
     
  7. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,971
    First Name:
    Wayne
    i would be build it up in note pad and make sure you have
    condition
    true
    false

    some times it can be much easier to layout in note pad with tabs - so you can follow the logic - as i have done and also that helps with the brackets - if you seperate onto seperate lines
    then once happy

    I copy to another notepad and take out all the spaces and paste into the formula line
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    Suggestion:

    I don't know what the maximum IFs are but what I have done with thes large ones is set each IF THEN ELSE in a separate cell

    That way I know if those parts work OK,

    Then you can start grouping.

    Trial and error
     
  9. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,971
    First Name:
    Wayne
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/901849

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice