November 9, 2016

I NEED A SITUATION WHERE IF ANY AMOUNT IS PAID, IT AUTOMATICALLY DISTRIBUTES THE PAID AMOUNT ACROSS THE GIVEN CELLS WITH THE CORRESPONDING AMOUNTS.

NOTE THAT ONE CELL MUST BE COMPLETE BEFORE OVERFLOWING TO THE NEXT.

I HOPE CATALIN NOW UNDERSTANDS WHAT I HAVE BEEN TRYING TO EXPLAIN

THANK YOU

November 9, 2016

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

November 8, 2013

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

Active Member

October 27, 2020

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.

November 8, 2013

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.

1 Guest(s)