IF statement maybe ?

[TURK]

Hi,

I'm in need of some help, it's just 'fine tuning' but I can not work it out.
The first thing I must do, is the 'Installer' IAIN in cell I15, is an independent contractor, so obviously would not be elegible for a bonus, cell W15.

So if the formula is "=SUM(F15*2%)" in column W.
Would I use the 'IF' statement to indecate 'IF IAIN' in column I, don't apply the 2% bonus ?

Example.xls
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	CustomerName	Installer	1	2	3	4	5	6	7	RefNo	InvNo	Paid	OrderNo	Payment	Outstanding	2%Bonus	NetProfit
2	SCOTT	AARON			1					237075	344067	90.69	PA12345	Under	-0.00	1.81	88.88
3	MORRIS	AARON			1					237077	344068	56.62	PA12345	Under	-0.00	1.13	55.49
4	WINZAR	JOHN		X										Under	-162.77	3.26	159.51
5	MOHUN	JOHN		1						237078	344069	100.80	PA12345	Over	0.00	2.02	98.78
6	OBRIEN	JOHN		1						237079	344070	55.58	PA12345	Over	0.00	1.11	54.47
7	GODDARD	JOHN		1						237080	344071	100.80	PA12345	Over	0.00	2.02	98.78
8	WILKINSON	OWAIN	X											Under	-65.68	1.31	64.37
9	ZIA	OWAIN	1							237081	344072	100.80	PA12345	Over	0.00	2.02	98.78
10	COUSINS	AARON			X									Under	-56.45	1.13	55.33
11	FAIRHEAD	AARON			1					237082	344073	90.69	PA12345	Under	-0.00	1.81	88.88
12	FAIRHEAD	AARON			X									Under	-65.68	1.31	64.37
13	DAWSON	AARON			1					237083	344074	56.45	PA12345	Under	-0.00	1.13	55.33
14	MCQUAID	OWAIN	1							237084	344075	56.62	PA12345	Under	-0.00	1.13	55.49
15	COOMBS	IAIN							1	237085	344076	90.69	PA12345	Under	-0.00	1.81	88.88
16	TURMEL	IAIN							1	237086	344077	56.62	PA12345	Under	-0.00	1.13	55.49
17	LABEY	OWAIN	X											Under	-123.30	2.47	120.83
Mon.2

 

[lenze]

In W2

IF(I2="IAIN",0,F2*2%)

Copy down

lenze

 

[texasalynn]

yes but you probably don't want to use specific names do you? I'm wondering if the indicator you have in column P is something that would work? So you're formula in w could be

=if(p2="",SUM(F2*2%),0)

 

[Smitty]

I'm not sure why you're using SUM(F15*2%), when F15*.02 should suffice. Since you're only evaluating one cell, you don't need SUM.

This should suffice:

=IF(I2="IAIN","",F2*0.02)

Hope that helps,

 

[TURK]

That was quick, thanks guy's. amazing to see there are so many variations. I will try them all out, and see which one works best.

Another broblem I have, strange one this one. In cell U2 I have this formula " =IF(S2=F2,"",IF(S2<F2,"Under",IF(S2>F2,"Over"))) ", indicating the amount paid, why is it showing 'Under' if the amount is correct. And further down the column, the amount paid is aslo correct, yet it's showing 'Over'. Any ideas ?