Home » Forums » Open Source Learning Center » Question and Answer

Complex IF statement

Submitted by Roger Johnson on 25 May, 2011 - 07:02

I need an IF statement please that will satisfy the following criteria -

Under 18 = £X
18 and over & "Student" = £X
18 to 65 = £Y
over 65 = £Z

Roger

‹ Convert File Names from Date to Day Linking to other files ›
»

Like this?

Submitted by Anonymous on 25 May, 2011 - 08:38.

If Occupation = Student Then
Amount = £X
Else
If Age < 18 Then
Amount = £X
ElseIf Age > 65 Then
Amount = £Z
Else 'should catch everything else
Amount = £Y
End If
End If

»

Complex

Submitted by Roger Johnson on 25 May, 2011 - 09:34.

I'm sure this works as a VBA statement in a macro, but I've tried slotting this into an EXCEL formula bar, which where I want it -

=If LEFT(F6,7) = "Student" Then AH6 = 0, ElseIf P6 < 18 Then AH = 0, ElseIf P6 > 65 Then AH = £10, Else =£20,End If

And it does not work.

»

Try this

Submitted by backwards on 25 May, 2011 - 11:40.

Put this in Cell AH6

=IF(LEFT(F6,7)="Student",0,IF(P6<18,0,IF(P6>65,10,20)))

»

Complex IF statement

Submitted by Roger Johnson on 25 May, 2011 - 12:50.

Sorry, but this does not work!!
The problem here is that the "Student" case is undone by the statement "IF(P6>65,10,20)" which resets ALL between 18 & 65 to 20, which includes Students.

Roger

»

I tested it on a blank

Submitted by backwards on 25 May, 2011 - 14:05.

I tested it on a blank worksheet.

Cell F6 = Occupation
Cell P6 = Age
Formula in Cell Q6 (because I don't want to scroll all the way to Column AH)

Put in the values:
F6 = Student
P6 = 10
Result = 0

Change P6 to anything, you still get 0. I think this was the intended effect?

Now put something else in F6, say "Teacher"
F6 = Teacher
P6 = 10
Result = 0

Change P6 to 20, Result = 20
Change P6 to 66, Result = 10

The Syntax is:
IF(test , true value, false value)

So break it down:

=IF(LEFT(F6,7)="Student",0,IF(P6<18,0,IF(P6>65,10,20)))

LEFT(F6,7)="Student" is the test
If its TRUE, then the value will always be zero. If its False, the next set of test happens:

P6<18 is the second test. If true, it will be zero. If false it will run the next test.

P6>65 is the third test. If true, it will be 10. If false, 20

»