# IF functions on excel

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

Not open for further replies.
1. ### LauraPearsonThread 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. ### etafModerator

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. ### etafModerator

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. ### LauraPearsonThread 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. ### LauraPearsonThread 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. ### etafModerator

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. ### etafModerator

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. ### KeebellahTrusted 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

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

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.

over 733,556 other people just like you!