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.

<tbody>
</tbody>

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

<tbody>
</tbody>

 

[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]

	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.712312546​	29​	22%​	2​
3​	1005993​	DRSG FOAM SOFT SIL SELF ADH SACRUM MEPILEX BORDER 9.2X9.2IN	8​	2.644963768​	16​	23%​	8.8​
4​	1004953​	BATT ALK PROCELL SZ AA 1.5V	22​	0.630603535​	19​	8%​	0​
5​	1004715​	ST PITCHER H2O INSUL FOAM OUTER JCKT LF	7​	3.370355089​	237​	95%​	8.05​
6​	1004577​	STRIP TEST BLD GLUCLOSE MNTR ACCU-CHEK INFORM II	7​	1.247219129​	45​	18%​	0​

Sheet: Sheet29

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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet9​

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

<tbody>
</tbody>

PERFECT!! Thanks.