Log In
Register
Home
November 9, 2016
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
November 9, 2016
Offline
Wow, Catalin, you are great. I am almost arriving there. Thank you so much
November 9, 2016
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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))). "
November 9, 2016
Offline
Catalin, you are great. You have been of great assistance.Thank you so much
Active Member
October 27, 2020
Offline
@catalin...you rock buddy even after 5 years your formulae gives a perfect solution
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Thank you
I guess a good formula never dies 🙂
Active Member
October 27, 2020
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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)
