After seven (7) conditions

[msk1972]

Below mentioned formula is working fine

=+IF(D7=E7,IF(AND(CT6>=85%,CT6<90%),3*30%*E7,IF(AND(CT6>=90%,CT6<100%),3*50%*E7,IF(AND(CT6>=100%,CT6<110%),3*100%*E7,IF(AND(CT6>=110%,CT6<125%),3*C7+3*120%*G7,IF(AND(CT6>=125%,CT6<140%),3*C7+3*140%*G7,IF(AND(CT6>=140%,CT6<150%),3*C7+3*150%*G7,"")&IF(AND(CT6>=150%),3*C7+3*160%*G7,"")))))))

and

Below mentioned formula is not working

=+IF(AND(CT6>=85%,CT6<90%,CU6=FALSE,CV6>=0%,CV6<100%),SUM(3*30%*E7,3*15%*F7),IF(AND(CT6>=90%,CT6<100%,CU6=FALSE,CV6>=0%,CV6<100%),SUM(3*50%*E7,3*25%*F7),IF(AND(CT6>=100%,CU6=FALSE,CV6>=0%,CV6<100%),SUM(3*100%*E7,3*50%*F7),IF(AND(CT6>=100%,CU6=FALSE,CV6>=100%,CV6<110%),SUM(3*100%*E7,3*50%*F7),IF(AND(CT6>=100%,CU6=FALSE,CV6>=110%,CV6<125%),SUM(3*C7,3*50%*F7,3*120%*G7),IF(AND(CT6>=100%,CU6=FALSE,CV6>=125%,CV6<140%),SUM(3*C7,3*50%*F7,3*140%*G7),IF(AND(CT6>=100%,CU6=FALSE,CV6>=140%,CV6<150%),SUM(3*C7,3*50%*F7,3*150%*G7,""))))&IF(AND(CT6>=100%,CU6=FALSE,CV6>=150%),SUM(3*C7,3*50%*F7,3*160%*G7,""))))))

can anyone help me in this regard.

Thanks in advance.

 

[Sal Paradise]

You can't nest more than 7 functions.

So IF(IF(IF(IF(IF(IF(IF()))))) is okay, but IF(IF(IF(IF(IF(IF(IF(IF())))))) isn't.

It looks like your formula can be simplified, but you'll have to break down your logic for us.

 

[msk1972]

Product Per Pack Rs.

A 18.00
B 23.00
C 28.00
D 09.00
E 10.00
F 15.00
G 11.00
H 03.00
I 07.00

Achievement Incentive (Per Pack)

85% to 89.99% 30%
90% to 99.99% 50%
100% to 100%
110% to 120%
125% to 140%
140% to 150%
Over 150% 160%

Example - 1:
Product (A): Target 200 Packs, achievement 200 packs, Incentive = Rs.200 x 18 = 3,600

Example - 2:
Product (A): Target 200 packs, achievement 230 packs, i.e. 115% calculation as follows:
(200 packs at Rs.18, & 30 packs at Rs.21.60) = 3,600 + 648 = 4,248

Example - 3:
Product (A): Target 200 packs, achievement 250 packs, i.e. 125% calculation as follows:
(200 packs at Rs.18, 50 packs at Rs.25.20) = 3,600 + 1,260 = 4,860

 

[Sal Paradise]

Lookup table:

Excel Workbook
	A	B	C	D	E
1	Product	Price		Target %	Incentive
2	A	18		0%	30%
3	B	23		90%	50%
4	C	28		100%	100%
5	D	9		110%	110%
6	E	10		120%	120%
7	F	15		140%	140%
8	G	11		150%	160%
9	H	3
10	I	7
Prices

Lookup formula:

Excel Workbook
	A	B	C	D
1	Product	Target	Actual	Total
2	B	640	998	27894.4
3	F	440	339	8136
4	G	180	198	2197.8
5	H	240	115	552
6	C	220	317	9962.4
7	E	400	592	6688
8	I	620	353	3953.6
9	H	200	264	830.4
10	H	360	281	1348.8
11	A	460	253	7286.4
12	G	780	796	8756
13	D	200	272	2577.6
14	A	240	329	6242.4
15	I	400	640	5488
16	A	400	220	6336
17	A	800	888	16142.4
18	B	520	733	18818.6
19	F	340	435	6810
20	D	320	499	5457.6
Summary

I'm sure this is not correct because your data seems to have gaps in it (100 - 110, 120 to 125, etc.), and I'm not positive about the logic you want used.

This looks up sales vs. target, and if the sales are less than the target multiplies the price of the product by 30 or 50% (depending on the ratio of sales to target) to get the total. If the sales are larger than the target, then it calculates the price of the target, and multiplies the price of the additional sales by the multiplier in the lookup table.