mulitple IF statements

**Back2Basics** · 12-04-2008, 01:18 PM

I am trying to using multiple if staements regarding one cell.

What I am trying to achieve is:

if b2=0, then null, however if b2=1, then arable, however if b2=2, then manged grassland, however if b2=3, then forestry/woodland, however if b2=4, then semi-natural vegetation, however if b2=5, then urban, however if b2=6, then water

I have these 2, but they don't work - what am i doing wrong?

=IF(B2=0,null, IF(B2=1, Arable, IF(B2=2, Managed_Grassland, IF(B2=3, Forestry/Woodland, IF(B2=4, Semi-natural_Vegetation, IF(B2=5, Urban, IF(B2=6, Water, NO_LAND_USE)))))))

=IF(B2=0,IF(B2=1,IF(B2=2,IF(B2=3, IF(B2=4,IF(B2=5, IF(B2=6, Water, null), Urban),Semi-natural_Vegetation), Forestry/Woodland), Maneged_grassland), Arable), null)

Cheers

**arthurbr** · 12-04-2008, 01:25 PM

You can start with this kind of formula :
=LOOKUP(b2,{0,1,2,3},{0,"arable","manged","forestry"}) and extend the ranges

If you don't want to hard code make a small table with the values in col1 and the results in col 2 and replace the values between braces with the ranges

**Back2Basics** · 12-04-2008, 01:30 PM

Thanks again Arthur, you gave me a great solution yesterday as well.

Cheers, very thankful!

**mewingkitty** · 12-04-2008, 01:35 PM

I'd go with a table and vlookup, but that's just me. I used to use if statements the same way you suggested initially, they get overly-cluttered fast though.

**martindwilson** · 12-04-2008, 02:20 PM

in answer to your original question

=IF(B2=0,null, IF(B2=1, Arable, IF(B2=2, Managed_Grassland, IF(B2=3, Forestry/Woodland, IF(B2=4, Semi-natural_Vegetation, IF(B2=5, Urban, IF(B2=6, Water, NO_LAND_USE)))))))
what you are doing wrong is not enclosing text in quotes!
=IF(B2=0,"null", IF(B2=1, "Arable", IF(B2=2, "Managed_Grassland", IF(B2=3, "Forestry/Woodland", IF(B2=4, "Semi-natural_Vegetation", IF(B2=5, "Urban", IF(B2=6, "Water", "NO_LAND_USE")))))))

**arthurbr** · 12-04-2008, 04:33 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

mulitple IF statements

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

mulitple IF statements

Hm,

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions