Counting Remainder from division and subtraction

[digitz]

Hello, I have a number in variable X. Variable X is a time for instance 10, 14.5, 17.9 hours.
I have n boxes which each contain 4 hours.
I need to figure out how many full 4 hour boxes X fits into, and fit the remainder into the last box.

Here is an example:

X=17.9 hours
Box 1 = 4
Box 2 = 4
Box 3 = 4
Box 4 = 4
Box 5 = 1.9

My excel formula must figure out how many boxes it will need and then allocate full box values (4) to each box until it reaches the final box which contain less than 4 hours. after that I need to multiple a percentage in each box as follows:

Box 1 total percentage = 4 * 0.8% 
Box 2 total percentage = 4 * 0.96% 
Box 3 total percentage = 4 * 1.16% 
Box 4 total percentage = 4 * 1.27% 
Box 5 total percentage = 1.9 * 1.39%

How is this possible? the MOD(number, divisor) function only give the remainder after division. I need how many it was divided and the remainder as a decimal.

 

[barry houdini]

Yes, you can use MOD for the remainder, for number of boxes try

=INT(X/4)

 

[digitz]

=INT(X/4) gives the number of full capacity boxes of 4.
That's just an indication that it has n full capacity boxes.
Now that I know the value of n, I need to multiply the percentages above from n1=4 * 0.8% to n4=4 * 1.39%
How can Excel guess that it has to multiply the above mentioned percentages in 4 for each full capacity 4 hour time box? And how can I make it do such a thing. Seems complex.

 

[digitz]

Let's take 17.9 hours for example.
INT(X/4) gives 4 meaning that we have 4 full 4 hour boxes. So:

Problem 1: I don;t know how to tell excel to multiply the sum of the first 4 percentages by 4: 4*(0.8+0.96+1.16+1.27)
Question: What if n>4? How does Excel retrieve the rest of the percentage constants? (I have the percentage constants in a separate column in another cell or worksheet)

Assuming problem 1 is solved, I use MOD(X/4) and figure out the remainder of INT(X/4), right?
Problem 2: How does Excel figure out the last percentage constant to be used to multiply with the remaining hours of the last time box? The last time box will have a logical value of <=4.
In other words how does Excel guess that is has to multiple 1.9 (remainder of MOD(X,4)) with 1.39% (constant percentage related to box 5)?

 

[barry houdini]

OK try this approach:

Assuming your "X" value is in A1 and you have %s in B1:B10 put this formula in C1 and copy down to C10

=MEDIAN(A$1-(ROWS(B$1:B1)-1)*4,0,4)*B1

 

[digitz]

Barry,

With a little modification, it's working like a charm.

Thanks for your help.

 

[digitz]

Barry,

Could you please explain what you did?