Create a column based on another

[JoaoFernandes]

Hello all,

I have a pretty simple doubt and would greatly appreciate if anyone could help me. I have a simple database with only two columns - Date and Items Purchased. It looks like this:

Date	Items purchased
1st July	30
2nd July	15
3rd July	0
4th July	0
5th July	0
6th July	35
7th July	0
8th July	0
9th July	40
10th July	40
11th July	0
12th July	0
13th July	0
14th July	0
15th July	40
16th July	40
17th July	10
18th July	0
19th July	0
20th July	15
21st July	25
22nd July	0

Im trying to create a new column named Distributed Items, which should be the items purchased distributed by dates. For this column, no single date can have more than 30 items purchased and the remaining ones should transition to the next date. Im trying to make it look like this:

Date	Items purchased	Distributed items
1st July	30	30
2nd July	15	15
3rd July	0	0
4th July	0	0
5th July	0	0
6th July	35	30
7th July	0	5
8th July	0	0
9th July	40	30
10th July	40	30
11th July	0	20
12th July	0	0
13th July	0	0
14th July	0	0
15th July	40	30
16th July	40	30
17th July	10	30
18th July	0	0
19th July	0	0
20th July	15	15
21st July	25	25
22nd July	0	0

Unfortunately I can't write a Multi Row Formula that fits. Does anyone have a clue? Thank you!

[patrick_digan · Accepted solution]

@JoaoFernandes Give the attached a try. I used a multi-row tool like this and then capped it to 30 in a formula tool:

[JoaoFernandes]

Thank you!!

[JoaoFernandes]

Hi again Patrick,

If I needed to have some negative values in my "Items Purchased" column (which means items returned, like this):

Date	Items purchased	Distributed items
6th July	35	30
7th July	0	5
8th July	0	0
9th July	30	0
10th July	-30	0
11th July	0	0
12th July	45	30
13th July	-5	10
14th July	0	0

How would you recommend a possible way to adjust the formula? Thanks again for your time.

[patrick_digan]

@JoaoFernandes Hopefully a simple modification to the multi-row will do the trick.

[JoaoFernandes]

Thanks, you're the man!