Stock allocation tool, need formula with 2 conditions

[crixdj1981]

Cartel1.xlsx

Hi there,

I would really appreciate your help. I need a formula that takes the total value on D7 (18 in the example attached), and allocates the stock from D10 to D27 with the conditions from C2 and C3 (so minimum 1 case and maximum 3 cases per store). Lastly, if the stock is not enough, the formula should priorities the ones with the high priority value from C10 to C27.
Many thanks for any help given
Cristian

[JohnTopley]

Are you still using Excel 2003 ??? If not, please update your profile as answers DO depend on knowing this information.

[crixdj1981]

Are you still using Excel 2003 ??? If not, please update your profile as answers DO depend on knowing this information.

No i’m not. But can you help me with that formula?

[crixdj1981]

Any help please?

[windknife]

I don't sure what you wnat, maybe

D10
=MIN(IF(D$7/18<$C$2, IF(ROWS(D$10:D10)<=D$7/$C$2,$C$2+IF(ROWS(D$10:D10)<=MOD(D$7,$C$2),1,0),0), D$7/18 + IF(ROWS(D$10:D10)<=MOD(D$7,18),1,0)),$C$3)

copied down and across.

[crixdj1981]

I don't sure what you wnat, maybe

D10
=MIN(IF(D$7/18<$C$2, IF(ROWS(D$10:D10)<=D$7/$C$2,$C$2+IF(ROWS(D$10:D10)<=MOD(D$7,$C$2),1,0),0), D$7/18 + IF(ROWS(D$10:D10)<=MOD(D$7,18),1,0)),$C$3)

copied down and across.

Hi there, many thanks!
It’s exactly what i need, but there is something wrong with the calculation, when stock is like higher that 25, it seems to allocate more than what’s available. It works fine with other number, it just doesn’t with all of them. Please can you fix it? Thanks again

[alansidman]

Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your current version.
Thanks

[windknife]

try this,

=MIN(IF(H$7/18<$C$2, IF(ROWS(H$10:H11)<=H$7/$C$2,$C$2+IF(ROWS(H$10:H11)<=MOD(H$7,$C$2),1,0),0), INT(H$7/18) + IF(ROWS(H$10:H11)<=MOD(H$7,18),1,0)),$C$3)

copied down.

[crixdj1981]

windknife THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
It works 100%