Range Conditional Calculation

[tijnar]

[TABLE="width: 774"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Range[/TD]
[TD]No. of Deliveries[/TD]
[TD]Inventive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0-4[/TD]
[TD][/TD]
[TD]Zero[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5-8[/TD]
[TD][/TD]
[TD] ₹ 5.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9-14[/TD]
[TD][/TD]
[TD] ₹ 10.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]15-18[/TD]
[TD][/TD]
[TD] ₹ 15.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]18 Onwards[/TD]
[TD][/TD]
[TD] ₹ 20.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]No. of Deliveries[/TD]
[TD]Inventive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]25[/TD]
[TD]???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]10[/TD]
[TD]???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
In the column A you see a range in nos. and column B has no of
deliveries that a person does,
Column C has the incentive that will be paid to the person for doing this in the range.
Suppose a person does :
4 deliveries : he gets nothing
7 deliveries : he gets 3*5=15
10 deliveries : he gets (from 5-8 he gets 4*5=20) + 2 deliveries within
range 9-14 ie. (2*10=20) So he get total of 20+20=40
25 deliveries: he gets Range 5-8 = 4 @5=20; Range 9-14=6 @10= 60;
Range 15-18=4 @15=60; and Range 18 onwards =8 @20 =160: Total incentive becomes 20+60+60+160= 300

What I want is that when I enter the no. of deliveries it formula should calculate the amount automatically? What should be the formula in this case.
Many thanks for the support.

 

[RickXL]

Hi and welcome to the MrExcel Message Board!

I am not sure I agree about your figure for 25 deliveries.

Are you saying that if there are 18 deliveries then the rate for the 18th delivery is 15.00 but if there are 19 deliveries then the rate for the 18th is now 20.00?

Otherwise:

Excel 2013
	A	B
1	Number of Deliveries	Incentive
2	1	0
3	2	0
4	3	0
5	4	0
6	5	5
7	6	10
8	7	15
9	8	20
10	9	30
11	10	40
12	11	50
13	12	60
14	13	70
15	14	80
16	15	95
17	16	110
18	17	125
19	18	140
20	19	160
21	20	180
22	21	200
23	22	220
24	23	240
25	24	260
26	25	280
Sheet1

Cell Formulas
Range	Formula
B2	=((MAX(4,MIN(A2,8))-4)*5)+((MAX(8,MIN(A2,14))-8)*10)+((MAX(14,MIN(A2,18))-14)*15)+((MAX(18,A2)-18)*20)

 

[pjmorris]

How about this: =MAX(0,F10-18)*($C$6-$C$5)+MAX(0,F10-14)*($C$6-$C$5)+MAX(0,F10-8)*($C$5-$C$4)+MAX(0,F10-4)*($C$4-$C$3)

which gives the same result as Rick, but is slightly differently constructed.