Nested If function

R

Ranjit kurian

I need Nested IF Function in excel cell for the below query

I have four columns (Age,Freq, Day,Month) and the condition is as follows

If Age is 0-30 and Freq is Monthly and Day is <61 and Month is current or
previous month then the result should be "gradeA"

If Age is 0-30 and Freq is Monthly and Day is <61 and Month is Not current
or previous month then the result should be "gradeB"

If Age is 1-30 and Freq is Monthly and Day is >61 and Month is current or
previous month then the result should be "gradeF"

If Age is 1-30 and Freq is Monthly and Day is >61 and Month is Not current
or previous month then the result should be "gradeD"

If Age is 31-60 and Freq is Queratly and Day is <121 and Month is current
months(example:from current month take three months backward oct,sep,Aug are
considered as current month) then the result should be "gradeH"

If Age is 31-60 and Freq is Queratly and Day is >121 and Month is Not
current months then the result should be "gradeK"

Queartly always considered as three months from current month
Annually always considered as 12 months from current month

If Age is 61-90 and Freq is Annually and Day is <180 and Month is current
months then the result should be "grade Annual"

ELSE : FALSE
 
R

Ranjit kurian

Example of table is as shown below

Age Freq Day Month
0-30 Monthly 60 Oct-08 gradeA
0-30 Monthly 59 Sep-08 gradeA
0-30 Monthly 58 Jan-08 GradeB
1-30 Monthly 61 Oct-08 GradeF
1-30 Monthly 63 Jan-08 GradeD
31-60 Queratly 120 Aug-08 GradeH
31-60 Queratly 119 Sep-08 GradeH
31-60 Queratly 123 Jan-08 GradeK
31-60 Queratly 120 Jan-08 GradeL
61-90 Annually 180 Oct-08 Grade Annual
 
R

Raz

hoping this will work

=IF(A1="0-30",IF(B1="Monthly",IF(C1<60,IF(D1<TODAY, "Grade A", "FALSE"))))

change the conditions for others.
 
R

Raz

missing this Today()

should be like this

=IF(A1="0-30",IF(B1="Monthly",IF(C1<60,IF(D1<TODAY(), "Grade A", "FALSE"))))

change the conditions for others.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top