Need help to solve this with a formula for drag and drop

[dupp]

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

 

[coulombevin]

Hi dupp,

Could you give access to your sheet to everybody in order to help you.

Thanks,

Vincent

 

[dupp]

Hi dupp,

Could you give access to your sheet to everybody in order to help you.

Thanks,

Vincent

Hey coulumbevin, sorry for the late reply, thank you for looking into it, i just did give you access, my pc went to repair and just came back.

My explanation might be confusing, actually what i want is just to expand, it stopped at 2187, like going further with 2188, 2189 with the same algorithm. I have been trying to figure out what formula is used, so i can drag and drop or something similar to go further. I have been trying to use the formula with "if", but it seems kinda complicated and nested with other formulas and i am limited with the formulas in excel.

So if i add +1 to for example Column E, Column I will get +10 and Column H will -50.
The same process for example Column D gets +1, then Column H gets +20 and Column G -100.
But there is a maximum to Column F, G and H, so it wont go above or below that maximum.

This data actually comes from a javascript and calculation is done in a php file, the person in charge of this website did not update anymore, not sure if you can understand more how the calculation works if i show you the php file.

 

[coulombevin]

Hi dupp,

Here's what I would do: *I am using the French version of Excel 365, so there might be some errors due to manual translation or discrepancies in the Excel user manual*

- Cell A2188

=SUM(B2188:E2188)

- Cell F2188

=IF(((B2188-B2187)*40)+((C2188-C2187)*-150)+F2187<=-150,-150,IF(((B2188-B2187)*40)+((C2188-C2187)*-150)+F2187>= 30,30,((B2188-B2187)*40)+((C2188-C2187)*-150))+F2187)

- Cell G2188

=IF(((C2188-C2187)*30)+((D2188-D2187)*-100)+G2187<=-100,-100,IF(((C2188-C2187)*30)+((D2188-D2187)*-100)+G2187>= 20,20,((C2188-C2187)*30)+((D2188-D2187)*-100))+G2187)

- Cell H2188

=IF(((D2188-D2187)*20)+((E2188-E2187)*-50)+H2187<=-50,-50,IF(((D2188-D2187)*20)+((E2188-E2187)*-50)+H2187>= 10,10,((D2188-D2187)*20)+((E2188-E2187)*-50))+H2187)

- Cell I2188

=((E2188-E2187)*10)+I2187

Bests regards,

Vincent