equally distribution into 12 months with conditions

[bibbi2]

Hi all,

I need your help please.. I've been squishing my brain..

So I have problem on how to make a formula to equally distributing the total amount into 12 months with restricting ranges of number that can be used.
Imagine that you have 12 books that need to be put in boxes with different sizes..Anyway please look the example below:

Note: I cannot attach the excel file, the dropdown menu doesn't work. Sorry for the trouble.

Example.JPG

[kvsrinivasamurthy]

Pl attach a sample file.

[bibbi2]

Hi kvsrinivasamurthy,

Please find the sample file.
The column F is a fixed amount column
The column E is total amount that want to be achieved from sum of F-R
Column B & C are the input range for column G-R. For example, 8000 is not allowed.

[JeteMc]

I am assuming that if the amount doesn't fall within one of the ranges that you would have to use at least the next higher permissible amount. Also that the distribution will not always be 12 months as using a larger amount reduces the number of months needed.
Based on those assumptions this proposed solution employs some helper columns which may be moved and/or hidden for aesthetic purposes.
The formulas for the helpers, S:U respectively are:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The formula that populates column G is:

Please Login or Register  to view this content.

The formula that populates columns H:R is:

Please Login or Register  to view this content.

Let us know if you have any questions.

[bibbi2]

Hi JeteMC,

Thank you for your prompt answer and solution!

I have question,
1. Could you please explain the logic behind this formula for H:R =IF(VALUE(MID(H$3,SEARCH("h",H$3)+1,3))>INT($T4),0,$G4)
2. What is the formula for H:R if the value on row 3 is not text , but Date like 31-Jan-18, 28-Feb-18, etc

[JeteMc]

Assuming that the dates in row 11 are supposed to be the last day of each month, paste the following formula into cell H11 then drag down and across as needed:

Please Login or Register  to view this content.

The formula that you ask about in post #5 compares the month number at the end of text string to the number of months it will take to complete the order.
The formula in this post does the same but uses the MONTH function to get the month number from the date.
Let us know if you have any questions.

[bibbi2]

Got It!

Thank you very much JeteMc !!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.