Excel Formula for Payout

[chaharmumesh]

Hi All,

I have data in which there some customer who had bought some oil in 2 different pack sizes 7.5 L and 10 L and in the Picture file attached i have given the number of bucket purchased for each pack size. Now i want to Give Payout in such a way that for Every 8 Buckets(in Multiples of 8) ,customers is given INR 6/L. The Order of buckets should be like first all the 10L buckets should be considered to give payout and then 7.5L Buckets.

for Eg.- lets say a customer has bought 10 buckets of 10 liters and 10 buckets of 7.5 liters. So total Buckets is 20.
Now as per the schemes every 8 buckets 6rs/ltr which means Payout can be give on 16 buckets because 16 is multiple of 8. Now i will consider all the 10 buckets of 10 Liters because they will contain higher volume as compared to 7.5 Liters.
Payout = (number of buckets of 10 liters * size of buckets + number of buckets of 7.5 liters * size of buckets )*6
(10*10+7.5*6)*6

 

[johnnyL]

See if this does what you want.
Copy the following formula into AF2:

=IF(INT(AE2/8)<1,0,IF(AD2<(INT(AE2/8)*8),((INT(AE2/8)*8)-AD2)*7.5*6+(AD2*10*6),(INT(AE2/8)*8)*10*6))

Then drag the formula down that column.

Here is the form I used, I just didn't go out to the Ax column, in other words instead of AC, I just used C, instead of AD, I just used D, etc.

Book1
	C	D	E	F	G
1	7.5L Buckets	10L Buckets	Total Buckets	Payout
2	20	8	28	1200
3	20	12	32	1620
4	15	10	25	1230
5	3	7	10	465
6	3	11	14	480
7	2	10	12	480
8	4	11	15	480
9	4	3	7	0
10
Sheet3

Cell Formulas
Range	Formula
E2:E9	E2	= C2 + D2
F2:F9	F2	=IF(INT(E2/8)<1,0,IF(D2<(INT(E2/8)*8),((INT(E2/8)*8)-D2)*7.5*6+(D2*10*6),(INT(E2/8)*8)*10*6))

 

[chaharmumesh]

See if this does what you want.
Copy the following formula into AF2:

=IF(INT(AE2/8)<1,0,IF(AD2<(INT(AE2/8)*8),((INT(AE2/8)*8)-AD2)*7.5*6+(AD2*10*6),(INT(AE2/8)*8)*10*6))

Then drag the formula down that column.

Here is the form I used, I just didn't go out to the Ax column, in other words instead of AC, I just used C, instead of AD, I just used D, etc.

Book1
	C	D	E	F	G
1	7.5L Buckets	10L Buckets	Total Buckets	Payout
2	20	8	28	1200
3	20	12	32	1620
4	15	10	25	1230
5	3	7	10	465
6	3	11	14	480
7	2	10	12	480
8	4	11	15	480
9	4	3	7	0
10
Sheet3

Cell Formulas
Range	Formula
E2:E9	E2	= C2 + D2
F2:F9	F2	=IF(INT(E2/8)<1,0,IF(D2<(INT(E2/8)*8),((INT(E2/8)*8)-D2)*7.5*6+(D2*10*6),(INT(E2/8)*8)*10*6))

Thank you so much , the formula is working fine

 

[Peter_SSs]

@johnnyL
I don't think that your formula needs that first IF section. In col F below I have just used the latter portion of your formula and it seems to produce the same results (& for other sample data that I tested).

@chaharmumesh
I thought that I would see if I could create a direct calculation without IF statements and came up with the col G formula below. I'm not suggesting that is is any better than the col F formula & in fact I would stick with the col F formula. However, since I was posting about that anyway, I thought that I might as well include mine too.

21 09 21.xlsm
	C	D	E	F	G
1	7.5L Buckets	10L Buckets	Total Buckets	Payout
2	20	8	28	1200	1200
3	20	12	32	1620	1620
4	15	10	25	1230	1230
5	3	7	10	465	465
6	3	11	14	480	480
7	2	10	12	480	480
8	4	11	15	480	480
9	4	3	7	0	0
Payout

Cell Formulas
Range	Formula
E2:E9	E2	= C2 + D2
F2:F9	F2	=IF(D2<(INT(E2/8)*8),((INT(E2/8)*8)-D2)*7.5*6+(D2*10*6),(INT(E2/8)*8)*10*6)
G2:G9	G2	=(MIN(D2,FLOOR(E2,8))*10+MAX((FLOOR(C2+MOD(D2,8),8)-MOD(D2,8))*7.5,0))*6