Hey there guys, need some help with a formula.
Column A has a total of Column B, C, D and E.
Column B = Column F, Column C = Column G, Column D = Column H, Column E = Column I.
Everytime in a new row, Column B, C, D or E will get +1, there is always two changes every row.
If there is a change in for example in Column I, then Column E gets +1
Column I = +10 (always positive)
Column H = +20(max negative -50, max positive 10), if Column I gets +10, Column H will get -50
Column G = +30(max negative -100, max positive 20), if Column H gets +20, Column G will get -100
Column F = +40(max negative -150, max positive 30), if Column G gets +30, Column F will get -150
It should always start checking Column I first, if adding +10 to Column I, then it needs to check Column H previous row, if previous row is for example -20 then adding another -50, it ends at -70, will go over the maximum negative, example for maximum positive is starting from 30 adding -50, it ends at 20, it goes over the maximum, so Column I will not add +10 and neither +1 to Column E. Doing the same for Column H, G and F. Add +20 to Column H means -100 to Column G.
I need a formula to check everytime if it went over the maximum, for example column H -60 and 20 is over maximum, so it will not add anything, starting from Column I, working from right to left, if so go to the next column and then add the rest of the Columns accordingly.
Column A stopped at a total of 2187, i want to drag and drop to go further with all the Columns.
See spreadsheet here:
https://docs.google.com/spreadsheets/d/1CknLQyH-pG6ZmCOB2VLvEdHk8IuYmVvDS4Nuu0TAVKs/edit?usp=sharing
Column A has a total of Column B, C, D and E.
Column B = Column F, Column C = Column G, Column D = Column H, Column E = Column I.
Everytime in a new row, Column B, C, D or E will get +1, there is always two changes every row.
If there is a change in for example in Column I, then Column E gets +1
Column I = +10 (always positive)
Column H = +20(max negative -50, max positive 10), if Column I gets +10, Column H will get -50
Column G = +30(max negative -100, max positive 20), if Column H gets +20, Column G will get -100
Column F = +40(max negative -150, max positive 30), if Column G gets +30, Column F will get -150
It should always start checking Column I first, if adding +10 to Column I, then it needs to check Column H previous row, if previous row is for example -20 then adding another -50, it ends at -70, will go over the maximum negative, example for maximum positive is starting from 30 adding -50, it ends at 20, it goes over the maximum, so Column I will not add +10 and neither +1 to Column E. Doing the same for Column H, G and F. Add +20 to Column H means -100 to Column G.
I need a formula to check everytime if it went over the maximum, for example column H -60 and 20 is over maximum, so it will not add anything, starting from Column I, working from right to left, if so go to the next column and then add the rest of the Columns accordingly.
Column A stopped at a total of 2187, i want to drag and drop to go further with all the Columns.
See spreadsheet here:
https://docs.google.com/spreadsheets/d/1CknLQyH-pG6ZmCOB2VLvEdHk8IuYmVvDS4Nuu0TAVKs/edit?usp=sharing
