Payout calculation

[chaharmumesh]

Hi All,

I have data in which there some customer who had bought some oil in 3 different pack sizes 7.5 L , 10 L and 15 L and in the Picture file attached i have given the number of bucket purchased for each pack size. Now i want Give Payout in such a way that for Every 5 Buckets ,customers is given INR 6/L. The Order of buckets should be like first 15 Liters buckets to be used than 10L and then 7.5

Thanks in advance

 

[SQUIDD]

Hi,

I must say i have been looking at this, and trying to understand exactly what you want.

I cannot even work out how you have worked out the figure 810 in column payout.

Perhaps you could explain.

Assume you need the formula to output the 810?

if so, please just expain the match to get there.

thanks

dave

 

[SQUIDD]

ok, so now worked out the Rs is Rupee?

i not even sure how you got to 19 buckets in this line of data.

 

[chaharmumesh]

ok, so now worked out the Rs is Rupee?

i not even sure how you got to 19 buckets in this line of data.

so if you sum up 14 buckets of 7.5 L , 3 buckets of 10 L and 2 buckets of 15 L, then the total is coming to be 19(14+3+2) buckets. and since the Payout is given to be given on every 5 buckets then the multiple of 5 is 15 ,hence maximum payout is to be given on 15 buckets .we cannot give payout on 19 buckets ,because 19 is not multiple of 5. Now the answer to question how i reached to 810 is that since 15 litres bucket contain the highest of volume of all three , so we will consider all the buckets of 15 Litre then 10 Litres buckets and then 7.5 litres buckets to complete 15 buckets. like(2*15+3*10+10*75)*6 . i am multiplying it by 6 because the schemes is every 5 buckets 6 Rs/ltr.
if require you can insert multiple columns to take out multiple of 5 , then number of buckets of 15 L ,10 L and 7.5 on which payout is to be given. not necessary to take solution in one column.

i hope this clarifies

 

[SQUIDD]

ok

so i think i got you

=(((ROUNDDOWN(E2/5,0)*5-D2-C2)*7.5)+D2*15+C2*10)*6

is the second answer 735??

dave

 

[SQUIDD]

forgot to say, you need to change the cells to match your data also

since i did not know what cells you was using.

i used c, d and e, and this refers to the 14, 3 and 2

 

[chaharmumesh]

ok

so i think i got you

=(((ROUNDDOWN(E2/5,0)*5-D2-C2)*7.5)+D2*15+C2*10)*6

is the second answer 735??

dave

Thank you for the solution and its the right answer for the second Row, can you pls attached the excel file so that i can see the refrences as when i am trying to use the same formula ,its not calculating right

 

[chaharmumesh]

Thank you for the solution and its the right answer for the second Row, can you pls attached the excel file so that i can see the refrences as when i am trying to use the same formula ,its not calculating right

Please help updating the formula as per the attached snapshot,

 

[SQUIDD]

=(((ROUNDDOWN(F2/5,0)*5-E2-D2)*7.5)+E2*15+D2*10)*6

in cell G2

try that

 

[SQUIDD]

oh yes

and assume you already have this, but this is in f2

=SUM(C2:E2)

Dave