CODEWITHSUNDEEP
excel
PAO3092
PAO3092

Reputation: 93

How do you add multiple statements to an IF Function?

I'm trying to add multiple statements to a formula so that it looks like this:

Note Column A begins at 2015 and descends

enter image description here

It cuts off at 2008 but pretend it goes down to at least 2006, which would be cell A10. The function goes in Column C so that cells in Column B meet the above criteria that I listed. Here C1 should have a 2 because it is in between 2006 and 2008 (cells A10 and A8), C2 should have a 3 because it is in between 2010 and 2012 (cells A6 and A4)...etc.

I do not know how to go about it however. I came up with this formula below, but it doesn't seem to work:

IF(B1=$A$1,5,IF(B1<$A$1,4,IF(B1<$A$3,3,IF(B1<$A$6,2,IF(B1<$A$10,1,0)))))

Would a VLOOKUP function work better here? And if so please share

This may seem redundant considering the years are next to each other, but I am using this in another spreadsheet to score the years 1 through 5, this is just an example.

Thank you.

Upvotes: 0

Views: 168

Answers (2)

Fratyx
Fratyx

Reputation: 5797

A little bit too late but shorter :-)

=CHOOSE(MATCH(B1,$A$1:$A$10,0),5,4,4,3,3,3,2,2,2,1)

Upvotes: 1

AJPerez
AJPerez

Reputation: 3595

Nesting IFs is OK, you almost got it right. What you're doing wrong is your conditional expressions.

  • If B1 is 2015, then C1 = 5. That should work.
  • If B1 is 2014, then C1 = 4. That works, too.
  • But what happens if B1 is 2011? 2011 is less than 2015 too... so your formula will make C1 = 4 instead of 3.

You can rewrite it like this:

=IF(B1=2015, 5,
    IF(B1>=2013, 4,
       IF(B1>=2010, 3,
          IF(B1>=2006, 2, 1)
       )
    )
 )

Or without the extra spaces: 

=IF(B1=2015,5,IF(B1>=2013,4,IF(B1>=2010,3,IF(B1>=2006,2,1))))

Another option would be to use a "translation table", and then use VLOOKUP instead. You would have the following columns:

D     E
----  ----
2015  5
2014  4
2013  4
2012  3
2011  3
...   ...

Your formula would be then something like:

=VLOOKUP(B1, D:E, 2, FALSE)

The parameters passed to VLOOKUP mean:

  • What are you looking for: the value in B1.
  • Where you will look it: your 2 new columns, D and E.
  • What column will the result be returned from: the second one, E.
  • FALSE because the data is not ordered ascending, and we want to find an exact match.

Upvotes: 1

Related Questions