Developing If Function Formula Assistance

[AliUAE]

After Greetings
Your assistance will be appreciated in developing sales incentives using Excel IF function formula where it will calculate and changes the price list rate based on total volumes of the products ordered during the month.
[TABLE="class: cms_table, width: 435"]
<tbody>[TR]
[TD="colspan: 2"]Volume Range[/TD]
[TD]Bonus/Penalty[/TD]
[TD][/TD]
[TD]Product[/TD]
[TD]Cost Price[/TD]
[TD]Volume[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]349[/TD]
[TD]Rate * 2%[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]$30.25[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]350[/TD]
[TD]449[/TD]
[TD]Rate * 1%[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]$249.89[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]450[/TD]
[TD]549[/TD]
[TD]No Changes[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]$125.00[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]550[/TD]
[TD]649[/TD]
[TD]Rate * -1%[/TD]
[TD][/TD]
[TD="colspan: 2"]Total Volume[/TD]
[TD]550[/TD]
[/TR]
[TR]
[TD]650[/TD]
[TD]749[/TD]
[TD]Rate * -2%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]750[/TD]
[TD]1000[/TD]
[TD]Rate * -3%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000+[/TD]
[TD][/TD]
[TD]Rate * -4%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Some Conditions Examples:
1- If the total monthly volume falls between 450-549, no sales incentive applied (no changes on the products rate).
2- If the total monthly volume falls between 350-449, 1% penalty will be added on the cost price.
3- If the total monthly volume falls between 550-649, 1% bonus will be reduced from the cost price.
4- If the total monthly sale volume exceeds 1000, 4% bonus will be reduced from the cost price.

Thanks in advance for getting all the assistances and comments.
Kind regards.
Ali

 

[AhoyNC]

Maybe something like this:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Volume Range	*	Bonus/Penalty	*	Product	Cost Price	Volume	Cost Change
2	0	349	2%	*	A	$30.25	300	29.95
3	350	449	1%	*	B	$249.89	50	247.39
4	450	549	0	*	C	$125.00	200	123.75
5	550	649	-1%	*	Total Volume	*	550	*
6	650	749	-2%	*	*	*	*	*
7	750	1000	-3%	*	*	*	*	*
8	1001	*	-4%	*	*	*	*	*
Sheet

 

[AliUAE]

Dear Ahoy
Appreciate your quick response and the efforts exhausted in developing the above formula, will try it and update you accordingly
Cheers
Ali