If Statement too long multiple conditions

smarksberry

New Member
Joined
Dec 15, 2005
Messages
15
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Try multiplying the C13 by 100 in your lookup statement.
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
Try tabulating the relationship between growth and payout, then use a vlookup()
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,910
Messages
5,574,994
Members
412,633
Latest member
simon_elvin
Top