Register
Log In
Unanswered
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
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
Catalin Bombea said
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
November 9, 2016
Offline
Meyanui said
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
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))). "
Most Users Ever Online: 57
Currently Online:
1 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Susan Booze
Rita Hrez
David Innes
preeti bhulabhai
Charles Klasek
Patrick Isedu
Emma Hayward
Vamseedhar G
NA NA
Max Neumann
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
