# Counting Remainder from division and subtraction

#### digitz

##### New Member
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:
Code:
``````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:
Code:
``````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.

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Yes, you can use MOD for the remainder, for number of boxes try

=INT(X/4)

=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.

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)?

Last edited:
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

Barry,

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

Thanks for your help.

Barry,

Could you please explain what you did?

Replies
2
Views
219
Replies
1
Views
328
Replies
1
Views
137
Replies
5
Views
393
Replies
12
Views
452

Threads
1,203,072
Messages
6,053,377
Members
444,659
Latest member
vuphihung

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back