# After seven (7) conditions

#### msk1972

##### New Member
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.

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.

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

Lookup table:
Excel Workbook
ABCDE
1ProductPriceTarget %Incentive
2A180%30%
3B2390%50%
4C28100%100%
5D9110%110%
6E10120%120%
7F15140%140%
8G11150%160%
9H3
10I7
Prices

Lookup formula:
Excel Workbook
ABCD
1ProductTargetActualTotal
2B64099827894.4
3F4403398136
4G1801982197.8
5H240115552
6C2203179962.4
7E4005926688
8I6203533953.6
9H200264830.4
10H3602811348.8
11A4602537286.4
12G7807968756
13D2002722577.6
14A2403296242.4
15I4006405488
16A4002206336
17A80088816142.4
18B52073318818.6
19F3404356810
20D3204995457.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.

Replies
4
Views
1K
Replies
25
Views
1K
Replies
8
Views
2K
Replies
13
Views
925
Replies
7
Views
164

### Forum statistics

1,196,389
Messages
6,014,999
Members
441,864
Latest member
sweezy40 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back