# Using IF/AND Formula for Multiple Conditions

1. ## Using IF/AND Formula for Multiple Conditions

I have a spreadsheet that I want a number (points) show up according to what's in Column A and Column E. Column A has a Data Validation list and in Columns C & D start and end dates are entered that calculate into months in Column E. I want Column F to equal the number points according to what is entered into Columns A & E. I tried to do a IF/AND formula that would put the correct points in Column F. I've tried the following formula's so far but to no avail...I've attached a sample copy of the spreadsheet I am working on.

IF(AND(E5>=5, 5 or more years of 2010 (12 pts)),12,IF(AND(E5>2<5, >2< 5 years of 2010 (9 pts)),9,IF(AND(E5<0>2, >0<2 years of 2010 (6 pts)),6,IF(AND(E5<=5, Non SCM 5 or more years (3 pts)),3,IF(AND(E5<5, Non SCM less than 5 years (1 pt)),1,0)))))

=IF(AND(A5=5 or more years of 2010 (12 pts), E5>=5),12,IF(AND(A5=>2< 5 years of 2010 (9 pts), E5>2<5),9,IF(AND(A5=>0<2 years of 2010 (6 pts), E5<0>2),6,IF(AND(A5= Non SCM 5 or more years (3 pts), E5<=5),3,IF(AND(A5=Non SCM less than 5 years (1 pt), E5<5),1,0)))))

IF((AND(A5=5 or more years of 2010 (12 pts), E5>=5),12,IF((AND(A5=>2< 5 years of 2010 (9 pts), E5>2<5),9,IF((AND(A5=>0<2 years of 2010 (6 pts), E5<0>2),6,IF((AND(A5= Non SCM 5 or more years (3 pts), E5<=5),3,IF((AND(A5=Non SCM less than 5 years (1 pt), E5<5),1,0)))))

I am an amateur who likes trying different formulas in Excel to try and make things a little simpler. I appreciate any assistance I can get

2. ## Re: Using IF/AND Formula for Multiple Conditions

Maybe in F5:
``Please Login or Register  to view this content.``

3. ## Re: Using IF/AND Formula for Multiple Conditions

protonLeah,

Thank you. The formula seems to be working except for the criteria "5 or more years of 2010 (12pts)". In Column F for the points it shows #N/A. I have never used a formula like this one before so I am not quite sure I understand it. I am not understanding the "LEFT(\$A5,3)="non",3^" part of the formula.

4. ## Re: Using IF/AND Formula for Multiple Conditions

Given Parameter: Non SCM 5+ years = 3 pts or, Non SCM < 5 years = 1 pt

Calculation: LEFT(\$A5,3)="non",3^((DATEDIF(C5,D5,"Y"))>=5)
If the first three characters in cell An are "non" then perform the calculation: Get the date difference in years and test. Greater than [or equal] 5 returns TRUE (i.e., 1), otherwise FALSE (0).
then 3 is raised to the calculated exponent ---> 30 --> 1 and 31--> 3

EDIT: Added "=" in equation above.

5. ## Re: Using IF/AND Formula for Multiple Conditions

protonLeah,

I am not sure what's happening but it still would not work for the "5 or more years of 2010 (12 pts). I've attached the spreadsheet to show what keeps happening. And thank you for the explanation of the equation. I really appreciate.

6. ## Re: Using IF/AND Formula for Multiple Conditions

F5:
``Please Login or Register  to view this content.``
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

7. ## Re: Using IF/AND Formula for Multiple Conditions

Okay...I guess I just don't get it because I can't get it to work just for that particular criteria. Thank you so much for you help. I truly appreciate it.

8. ## Re: Using IF/AND Formula for Multiple Conditions

o.k. I;ve posted the formula in F5:F10

9. ## Re: Using IF/AND Formula for Multiple Conditions

protonLeah....Thank you sooooo much. I truly appreciate you. The formula works like a charm.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1