Subtract 1 from last 5 records

[kermittaipan]

After trying countless of hours I almost don’t want to ask for help cause I want solve it myself but it's time to toss in the towel and turn to someone that can guide me.

If value in Column A is -1 I want the last 5 records (rows) in Column B to also be -1. If the cell in Column B already has value -1 I want it to decrease to -2. New values to columns will be continuously added.


A B
0 0
0 -1
0 -1
0 -1
0 -2
-1 -2
0 -1
0 -1
-1 -1
0 0
0 0
etc. etc.

Data is in a table, I am not using VBA and version is Microsoft 365.

Kindly let me know if any additional information is needed.

Many thanks!

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[kermittaipan]

Thanks for the welcome!

Please see attached manual example.

[bebo021999]

Pls explain:
A6=0, B6=-2 (should be -1?)
A10=-1, B10=-1 (should be -2?)

[kermittaipan]

Thanks for the quick response.

A6=0, B6=-2
Because B6 is within previous 5 records from A10 and A7 B6=-2. (B5=-1 because it is 6 records from A10 hence it is excluded)

A10=-1, B10=-1
Because next 5 records from A10 do not contain -1

[kermittaipan]

Perhaps it’s unclear. I’d like a function for:

If A8=-1 then B8=B8-1, B7=B7-1, B6=B6-1, B5=B5-1, B4=B4-1

Your ideas on how to do this is welcomed. Thanks.

[Bo_Ry]

Maybe try at B2
=SUM(A2:A6)

[kermittaipan]

Thank you!

Haha! I've been blinded by my attempts of using endless IF statements with Count etc. It's so simple when I see it!

However, the total/final formula I am looking for has more to it but Ill hack away before potentially seeking guidance again.

Again, Thanks!

[kermittaipan]

Im back…

Although the spreadsheet works as it is it I would like to make it “leaner” so that there are only 3 columns instead of 4.

How would I go about setting up the SUM() function so that it also calculates A-B?

[Exl-Noob]

Hi kermittaipan,

You changed something in your data. The value isn't always lower in column A compare to column B, so a simple substraction would provide positive results instead of negative ones.
After using a lot of ABS() here is the end result attached for your enjoyment.

Cheers.

[Bo_Ry]

Please try

=-SUMPRODUCT(ABS(G3:G7-H3:H7))

[kermittaipan]

Exl-Noob and Bo_Ry,

thank you for the solutions. They both work perfectly!

I will continue to fiddle around with this to try to reach my final formula.

Thanks and I might be back with a questions.

[kermittaipan]

Finally managed to get final formula done. I realize it is different from what I originally asked but I just wanted to a hint in order to learn by doing.

I came up with:

=SUM(IFS((A3:A22-B3:B22)<=-10;-2;(A3:A22-B3:B22)<=-7;-1;(A3:A22-B3:B22)>-7;0))

If it could be improved/more efficient let me know.

Thank you for the help!