nesting IF statements

[ozzyc]

Hi,
i am using excel 2007, and am trying to make a formula that if
b5 is below 70%, b17 gets multiplied by 0
b5 is between 70% and 100%, b17 gets multiplied by 5
b5 is between 100% and 110%, b17 gets multiplied by 8
b5 is above 110%, b17 gets multiplied by 10

So far I have tried =IF(b5<70%, b17*0, IF(AND(b5>70%, b5<100%, B17*5, IF(AND(b5>100%, b5<110%, b17*8, IF(b5>110%, b17*10))))))

Excel says theres an error in my formula but I do not see it,
would love any help!

 

[bobsan42]

=IF(b5<70%, b17*0, IF(AND(b5>70%, b5<100%), B17*5, IF(AND(b5>100%, b5<110%), b17*8, IF(b5>110%, b17*10))))
and there were 2 ) too many at the end

Consider using the function wozard to avoid this type of problems

 

[jbeaucaire]

Try this:

=$B$17 * LOOKUP(B5, {0,0.7,1,1.1}, {0,5,8,10})

Excel Workbook
	B	C
5	110%	250
6	55%	0
7	83%	125
8
9
10
11
12
13
14
15
16
17	25
Sheet1

 

[Aladin Akyurek]

Try...

=B17*LOOKUP(B5,Percentages,Multipliers)

where percentages and multipliers in a 2 column table on a separate sheet like below:

<TABLE style="WIDTH: 111pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=147><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2787" width=78><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2446" width=69><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=78>Percentages</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=69>Multipliers</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>0%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>70%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>100%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>8</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>110%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>10</TD></TR></TBODY></TABLE>

Name (using Name Box) A2:A5 as Percentages and B2:B5 as Multipliers.

You can also invoke (without such a table)...

=B17*LOOKUP(B5,{0;0.7;1;1.1},{0;5;8;10})

 

[ozzyc]

wow, thanks for all the help!