• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating Maximum Number of Divisions before Remainder from Division is less than denominator and expressing Remainder as a fraction of denominator

HansrajR

Member
I am seeking to calculate the number of days required to process a defined tonnage (2,697.030 tons in the example - B2) as from a set date (29/06/2021 in the example-C2), taking into account the maximum tonnage (Quota) for corresponding subsequent dates available in a table.

On event, on the last day, the residual amount is less than the maximum tonnage (Quota), the residual amount is expressed as a fraction of the Quota.

Ultimately, the value of Cell D2 is obtained by summing number of divisions and remainder divided by Quota for corresponding date.

Please find attached sample Excel document.

74625
 

Attachments

  • Sample Excel Document.xlsx
    14.3 KB · Views: 4
Please try

=MATCH(B2,I2:I6)+TREND({0;1},INDEX(I2:I6,MATCH(B2,I2:I6)):INDEX(I2:I6,MATCH(B2,I2:I6)+1),B2)

or shorter but volatile

=MATCH(B2,I2:I6)+TREND({0;1},OFFSET(I1,MATCH(B2,I2:I6),,2),B2)
 

Attachments

  • Sample Excel Document.xlsx
    14.5 KB · Views: 4
Thanks for the much appreciated input.

I tried the provided solution and it produces required results when working with one row and when the cumulative sum table for set Tonnage i.e. 2,697.030 tons in the row is available.

The complexity increases when there are more batches added.

A new issue arises with formula I used. For example, after processing Batch A on 29-Jul-21 and 30-Jul-21 (assigned Quota for both dates is fully reached), the remaining 1,066.470 tons of Batch A is completed on 01-Jul-2021.

Since the Quota of 1,425.41 ton set for 01-Jul-2021 is not reached, processing of the next Batch B is started.

Kindly find attached my workings to calculate processing time for Batch B and subsequent batches.

But the formula I used to calculate processing time of Batch B does not account for 1,066.470 tons already used up by Batch A which reduces the remaining Quota to process Batch B on 01-Jul-21 to (1,425.41-1,066.470) =358.94 tons.

74635
 

Attachments

  • Sample Excel Document.xlsx
    31.3 KB · Views: 1
Maybe try

M2
=SUM(L2,M1)

J2
=IFERROR(MATCH(SUM(B$2:B2),$M$2:$M$34)+TREND({0;1},INDEX($M$2:$M$34,MATCH(SUM(B$2:B2),$M$2:$M$34)):INDEX($M$2:$M$34,MATCH(SUM(B$2:B2),$M$2:$M$34)+1),SUM(B$2:B2))-SUM(J$1:J1),"Over")
 

Attachments

  • Sample Excel Document.xlsx
    33.3 KB · Views: 6
Back
Top