EXCEL - Nested IF Functions

[funkylad20]

I am trying to use a nested if formula using text rather than numbers from validation drop down options.

Cell A1 = "Term Time Only" OR "Full Time"
Cell A2 = "Autumn", "Spring", "Summer"

I want Cell A3 to read as:
If Cell A1 = Term Time Only AND Cell A2 = Spring, END RESULT IN CELL A3 IS THE FIGURE 12

BUT I need Cell A3 to then read as:
If Cell A1 = Full Time AND Cell A2 = Autumn, END RESULT IN CELL A3 IS THE FIGURE 16

There are differnet options for different cells, dependent on two cells with options in each.

I have mastered the initial part, but NOT the second and third part of the formula....so far I have:

=IF(A2="Autumn Term 2008",IF(A1="Term-Time Only",14,IF(A1="Full Time",16))))

[Bushwhacker]

Here's a function I wrote which returns a value of 0, 1, 2, 3 or 4 dependent on the contents of cells A1 & A2 :

If there is an A in A1 and a C in A2 then the function will return a 1

If there is an A in A1 and a D in A2 then the function will return a 2

If there is a B in A1 and a C in A2 then the function will return a 3

If there is a B in A1 and a D in A2 then the function will return a 4

If the combination is none of the above then the function will return a 0


=IF(AND(A1="A",A2="C"),1,IF(AND(A1="A",A2="D"),2,IF(AND(A1="B",A2="C"),3, IF(AND(A1="B",A2="D"),4,0))))

Just substitute the text in quotes with your own text and the 0, 1, 2, 3 & 4 with your numbers.

Give it a go and let me know how you get on.

BW

[Bushwhacker]

I though I would try it out & here it is :


=IF(AND(A1="Term Time Only",A2="Autumn"),1,IF(AND(A1="Term Time Only",A2="Spring"),2,IF(AND(A1="Term Time Only",A2="Summer"),3, IF(AND(A1="Full Time",A2="Autumn"),4,IF(AND(A1="Full Time",A2="Spring"),5,IF(AND(A1="Full Time",A2="Summer"),6,0))))))

Just substitute your numbers for the 1,2,3,4,5 & 6

BW

[funkylad20]

Works, fantastic. Thank you!