If Statement too long multiple conditions

[smarksberry]

Hi! I need help!

I tried to write an IF statement but it got too long. I have a column of % growths like so:

Growth
18%
20%
5%
10%
9%
14%
3%

And in the next column I have % Payout wich needs to look at the individual cells and return a % value based on the % in the growth column.

I had so far:
=(IF(C12<=10%,"0",IF(C12=11%,"2%",IF(C12=12%,"3%",IF(C12=13%,"4%",IF(C12=14%,"6%",IF(C12=15%,"8%",IF(C12=16%,"10%",IF(C12=17%,"12%")))))))))

But I need to include in my if statement:
18%, "14%"
19%, "16%"
20%, "18%"
>21%, "20"

I also tried to use:
=LOOKUP(C13,{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21},{"0%","0%","0%","0%","0%","0%","0%","0%","0%","0%","0%","2%","3%","4%","6%","8%","10%","12%","14%","16%","18%","20%"})

But as long as the growth column is formatted with %, it won't recognize it.

What should I do???

Sharon

 

[NBVC]

Try multiplying the C13 by 100 in your lookup statement.

 

[SteveO59L]

Try tabulating the relationship between growth and payout, then use a vlookup()

 

[barry houdini]

As NBVC says you need to multiply by 100 or change the lookup range. Also you don't need to list all percentages, just the boundaries. You could use this

=LOOKUP(C13*100,{0,11,12,13,14,15,16,17,18,19,20,21},{0,2,3,4,6,8,10,12,14,16,18,20})/100

format as percentage

....and Welcome to the board

 

[smarksberry]

HOT DOG! Multiplying/dividing by 100 worked! Thank you!!!


I LOVE THIS SITE!!!!!!