PARTIAL MONTH DISTRIBUTION

[Ari]

Hello,

I am looking for a formula that will help with the following distribution
question:

Take a quantity of items (for example 1.5 apples) and distribute them
accross the months which are available to that item (for example Jan, Feb,
and .4 of March). I always want to fill capacity in the lastest available
month first.

So to lay out the above example with a little more complexity (2 fruits)

A1 = 1.5
A2 = 2.2
B1 = Apples
B2 = Bananas

A3 = Jan
B3 = Feb
C3 = Mar
D3 = Mar
E3 = Apr
F3 = May

A4 = Apples
B4 = Apples
C4 = Apples
D4 = Bananas
E4 = Bananas
F4 = Bananas

A5 = 1
B5 = 1
C5 = .4
D5 = .6
E5 = 1
F5 = 1

The formula would go in A6:F6 and would result in

A6: 0.1
B6: 1.0
C6: 0.4
D6: 0.2
E6: 1.0
F6: 1.0

I can see doing this with different IF statements in each of the cells A6:F6
but since I have many fruits and need to be able to often change the months
different fruits are available (values in row 4 and 5) frequently, I am
looking for a uniform formula I can copy into all cells in row 6.

Thanks in advance for your help!

Ari Blum

 

[smartin]

Hello,

I am looking for a formula that will help with the following distribution
question:

Take a quantity of items (for example 1.5 apples) and distribute them
accross the months which are available to that item (for example Jan, Feb,
and .4 of March). I always want to fill capacity in the lastest available
month first.

So to lay out the above example with a little more complexity (2 fruits)

A1 = 1.5
A2 = 2.2
B1 = Apples
B2 = Bananas

A3 = Jan
B3 = Feb
C3 = Mar
D3 = Mar
E3 = Apr
F3 = May

A4 = Apples
B4 = Apples
C4 = Apples
D4 = Bananas
E4 = Bananas
F4 = Bananas

A5 = 1
B5 = 1
C5 = .4
D5 = .6
E5 = 1
F5 = 1

The formula would go in A6:F6 and would result in

A6: 0.1
B6: 1.0
C6: 0.4
D6: 0.2
E6: 1.0
F6: 1.0

I can see doing this with different IF statements in each of the cells A6:F6
but since I have many fruits and need to be able to often change the months
different fruits are available (values in row 4 and 5) frequently, I am
looking for a uniform formula I can copy into all cells in row 6.

Thanks in advance for your help!

Ari Blum

Hi Ari, This might give you some ideas. Hope it formats OK.

I laid out the availability data (your row 5) like this in A9:F11. It's
one row per fruit, one column per month:

Jan Feb Mar Apr May
Apples 1 1 0.4 0 0
Bananas 0 0 0.6 1 1

The distribution is in A14:F16 like this:

Jan Feb Mar Apr May
Apples 0.1 1 0.4 0 0
Bananas 0 0 0.2 1 1

Formulas: There are two. One is for the final month (May), the other
works in all preceding months.

May @ F15 (Apples): =MIN(F10,$A1-F10) (fill down for Bananas)
Jan @ B15 (Apples): =MIN(B10,$A1-SUM(C15:$F15)) (fill down and right for
Bananas through Apr).

From here it should not be difficult to adapt it so you only need to
change the fruits and availability in one place and the distributions
should update automatically. You will need to fix formulas though when
adding a new month.

Hope it helps.