Hi Meyanui,

Try this formula in F5:

=IF(AND(F$2>0,$D5-SUM($E5:E5)>0),MIN(F$2,$D5-SUM($E5:E5)),0)

You can copy it to the right and down as needed.

Sample file also attached.

Wow, Catalin, you are great. I am almost arriving there. Thank you so much

If I may ask, what is the essence of reading the empty cell (E5:E5).

Suppose that column (E) is deleted to make my work appear neat what can I do? 

I asked because when I did it affected the other cells across.

An explanation of this formula would be of great help. 

Thank you once more

You better hide that column, because the formula needs a starting point, to calculate the cumulated values. And that starting point cannot be in column D, and not in column F, where we have the first values.

SUM($E5:E5) , when you copy the formula to the right, will expand: in the last column will be SUM($E5:P5) (the $ sign locks the column reference for the first range, only the second range will increase)

Basically, the formula can be translated in:

"If there is a value in row 2, current column ($F2>0), AND the PAID amount is higher than the cumulated values , ($D5-SUM($E5:E5)>0), Return the minimum value between the row 2 value and the cumulated values (MIN(F$2,$D5-SUM($E5:E5))). "

Catalin, you are great. You have been of great assistance.Thank you so much

@catalin...you rock buddy even after 5 years your formulae gives a perfect solution

Thank you

I guess a good formula never dies 🙂

Dear Catalin,

need some assistance with attached file

Actually i used your formulae and everything works great but what i am stuck at is if i have a issue with a particular location i need to distribute its Quantity in available regions based on distance from Location 1 , independently for all rows 

example i have 5 locations and location 1 has issue so i cannot store the goods there , so i need formulae to distribute goods of location 1 into other 4 based on my priority, say first priority location 2 then 5 and then 4 and last 3

 how can ammend yiur formulae and  ensure quantity are distributed based on % say  60 % should go to first cell then 20% next cell and remain in 3rd cell 

Will highly appreciate you thoughts and solution.

try this one in cell G5:

=IF(G4="Yes","Issue",IF(AND(G$1>0,$E5-SUM($F5:F5)>0),MIN(SUM($G$1:G1)-SUM($F5:F5),$E5-SUM($F5:F5)),0))

It will relocate only to the next store, the percentage distribution is hard to achieve, much easier with visual basic i think. Or power query.

Hey I have a similar usecase that would require the use of vlookups

I made a separate thread;

My thread

Please do take a look,

Cheers,

Kevin