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
Bookmarks