Multiple IF statements

[ScottHe]

Trying to write an IF statement that will return the following in a new cell.

If the "% replenished/visit" is greater than 20% then return "avg. qty Ord/visit" - otherwise return 0.

However if "% replenished/vist" is greater than 20% and the "std. Dev." is over 2 then add 10% to "avg. qty ord./visit" and if over 3 then add 15% - otherwise do nothing.


[TABLE="width: 796"]
<tbody>[TR]
[TD="width: 70"][/TD]
[TD="width: 242"][/TD]
[TD="width: 123"][/TD]
[TD="width: 84"][/TD]
[TD="width: 147"] [/TD]
[TD="width: 130"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Description[/TD]
[TD]Avg. Qty Ord./visit[/TD]
[TD]Std. Dev.[/TD]
[TD]# of times ordered per Year[/TD]
[TD]% replenished/visit
[/TD]
[/TR]
[TR]
[TD]1012378[/TD]
[TD]CUSH WC PREINFLATED SEAT WAFFLE 17X17IN LF N/S REUS[/TD]
[TD]2[/TD]
[TD]0.712312546[/TD]
[TD]29[/TD]
[TD]12%[/TD]
[/TR]
[TR]
[TD]1005993[/TD]
[TD]DRSG FOAM SOFT SIL SELF ADH SACRUM MEPILEX BORDER 9.2X9.2IN[/TD]
[TD]8[/TD]
[TD]2.644963768[/TD]
[TD]16[/TD]
[TD]6%[/TD]
[/TR]
[TR]
[TD]1004953[/TD]
[TD]BATT ALK PROCELL SZ AA 1.5V[/TD]
[TD]22[/TD]
[TD]0.630603535[/TD]
[TD]19[/TD]
[TD]8%[/TD]
[/TR]
[TR]
[TD]1004715 [/TD]
[TD]ST PITCHER H2O INSUL FOAM OUTER JCKT LF[/TD]
[TD]7[/TD]
[TD]3.370355089[/TD]
[TD]237[/TD]
[TD]95%[/TD]
[/TR]
[TR]
[TD]1004577[/TD]
[TD]STRIP TEST BLD GLUCLOSE MNTR ACCU-CHEK INFORM II[/TD]
[TD]7[/TD]
[TD]1.247219129[/TD]
[TD]45[/TD]
[TD]18%[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F	G
1	Item	Description	Avg. Qty Ord./visit	Std. Dev.	# of times ordered per Year	% replenished/visit
2	1012378	CUSH WC PREINFLATED SEAT WAFFLE 17X17IN LF N/S REUS	2	0.71231255	29	12%	0
3	1005993	DRSG FOAM SOFT SIL SELF ADH SACRUM MEPILEX BORDER 9.2X9.2IN	8	2.64496377	16	6%	0
4	1004953	BATT ALK PROCELL SZ AA 1.5V	22	0.63060354	19	8%	0
5	1004715	ST PITCHER H2O INSUL FOAM OUTER JCKT LF	7	3.37035509	237	95%	8.05
6	1004577	STRIP TEST BLD GLUCLOSE MNTR ACCU-CHEK INFORM II	7	1.24721913	45	18%	0
Sheet9

Cell Formulas
Range	Formula
G2	=IF(F2>0.2,C2*(1+LOOKUP(D2,{0,2,3;0,0.1,0.15})),0)

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Item[/td][td]Description[/td][td]Avg. Qty Ord./visit[/td][td]Std. Dev.[/td][td]# of times ordered per Year[/td][td]% replenished/visit[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

1012378​

[/td][td]CUSH WC PREINFLATED SEAT WAFFLE 17X17IN LF N/S REUS[/td][td]

2​

[/td][td]

0.712312546​

[/td][td]

29​

[/td][td]

22%​

[/td][td]

2​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

1005993​

[/td][td]DRSG FOAM SOFT SIL SELF ADH SACRUM MEPILEX BORDER 9.2X9.2IN[/td][td]

8​

[/td][td]

2.644963768​

[/td][td]

16​

[/td][td]

23%​

[/td][td]

8.8​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

1004953​

[/td][td]BATT ALK PROCELL SZ AA 1.5V[/td][td]

22​

[/td][td]

0.630603535​

[/td][td]

19​

[/td][td]

8%​

[/td][td]

0​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

1004715​

[/td][td]ST PITCHER H2O INSUL FOAM OUTER JCKT LF[/td][td]

7​

[/td][td]

3.370355089​

[/td][td]

237​

[/td][td]

95%​

[/td][td]

8.05​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

1004577​

[/td][td]STRIP TEST BLD GLUCLOSE MNTR ACCU-CHEK INFORM II[/td][td]

7​

[/td][td]

1.247219129​

[/td][td]

45​

[/td][td]

18%​

[/td][td]

0​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet29[/td][/tr][/table]

Formula in cell G2:
=IF((F2>0.2)*(D2<=2),C2,IF((F2>0.2)*(D2>2)*(D2<3),1.1*C2,IF((F2>0.2)*(D2>3),1.15*C2,0)))

 

[ScottHe]

This?

Excel 2010

	A	B	C	D	E	F	G
Item	Description	Avg. Qty Ord./visit	Std. Dev.	# of times ordered per Year	% replenished/visit
CUSH WC PREINFLATED SEAT WAFFLE 17X17IN LF N/S REUS
DRSG FOAM SOFT SIL SELF ADH SACRUM MEPILEX BORDER 9.2X9.2IN
BATT ALK PROCELL SZ AA 1.5V
ST PITCHER H2O INSUL FOAM OUTER JCKT LF
STRIP TEST BLD GLUCLOSE MNTR ACCU-CHEK INFORM II

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1012378[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]0.71231255[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1005993[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]2.64496377[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1004953[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"]0.63060354[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1004715[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]3.37035509[/TD]
[TD="align: right"]237[/TD]
[TD="align: right"]95%[/TD]
[TD="align: right"]8.05[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1004577[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]1.24721913[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]18%[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IF(F2>0.2,C2*(1+LOOKUP(D2,{0,2,3;0,0.1,0.15})),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

PERFECT!! Thanks.