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

Division Function and/or QUOTIENT function in Excel with dynamic divisor (denominator) from Table changing by Criteria

HansrajR

Member
I am seeking to calculate Number of Processing Days based on a daily quota.

As long as the daily quota is constant, the calculation is simple i.e. Tonnage divided by the daily quota (as in Figure 1) gives the Number of days required to process the batch.

The result from the division above added to the Processing start date gives the processing End Date.

The calculation becomes complex when the daily quota changes along the days and the Processing End Date is unknown.

I am looking for a iterative formula to use in Excel to calculate the Number of Processing Days which accounts for changes in daily quota available in another table.

74614

Figure 1

A method of calculating Number of Days to process Batch A accurate to decimal places is preferred.

In the example, the 4th day will not be used completely to finish processing Batch A.

The implication of calculating number of processing days correct to decimal places is to determine the Processing Start Date of the next Batch B.

On event, number of processing days of Batch A is an integer implying the whole day will be exhausted, and therefore the Processing date of the next Batch B will be Processing End Date of Batch A plus one day.

Else, if number of processing days of Batch A has decimals, this will imply the last day will not be exhausted completely and Processing Start Date of Batch B will be on the same date as Processing End Date of Batch A as illustrated in Figure 2.

74615

Figure 2
 
I read about Excel SEQUENCE and MOD functions. A formula similar to the formula below might hold the solution

Excel 365 formula

=FILTER(SEQUENCE(C2), MOD(C2,SEQUENCE(C2))=0)

Array formula for previous Excel versions:

=SMALL(IF(MOD($C$2,SEQUENCE($C$2))=0,SEQUENCE($C$2),""),ROWS($A$1:A1))
 
To calculate the Processing End Date of Batch A, bosco_yip provided solution to calculate the Processing End Date of Batch A using the following formula:

=C2+MATCH(B2,SUMIF(OFFSET(INDEX(H:H,MATCH(C2,G:G,0)),,,ROW($1:$8)),">0"))+1

A formula is required to calculate the Number of Processing Days accurate to decimal places.

A sample Excel file is attached.

74620
 

Attachments

  • Sample Excel Document.xlsx
    10.3 KB · Views: 1
Last edited:
HansrajR
You have already noticed to reread Forum Rules
Those rules are for You too.
There are similar rules in every Forum eg about Cross-posting.
If You have any questions about Forum Rules, please ask.
My apologies, I am joined the forum just yesterday. I will keep in mind Forum Rules in future. Thanks for notifying me.
 
HansrajR
What did You apologies?
You wrote that You joined the Forum just yesterday - that's great.
Of course, Your the first duty was yesterday to read those Forum Rules.
 
Back
Top