Calculation in an Excel worksheet using values from another worksheet based on condition

[HansrajR]

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

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.

 

[bosco_yip]

Try,

1] In "End date" F2, array (CSE) formula :

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

2] In "No. of days" E2, formula :

=F2-C2+1

 

[HansrajR]

Try,

1] In "End date" F2, array (CSE) formula :

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

2] In "No. of days" E2, formula :

=F2-C2+1

View attachment 74608

Thanks for your much appreciated input.

Is there an alternative to calculate Number of Days to process Batch A accurate to decimal places.

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.

 

[bosco_yip]

Thanks for your much appreciated input.

Is there an alternative to calculate Number of Days to process Batch A accurate to decimal places.

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.

This is the forum rule, one post one question.

Please open a new post for your new question with explanation and attachment

Regards

 

[HansrajR]

Thanks for the note. I just joined.
I edited the question, a new post will be a repetition of the question.

 

[bosco_yip]

Thanks for the note. I just joined.
I edited the question, a new post will be a repetition of the question.

Don't edit your original post in post #.1

Open a new post for your new question

This post is closed.