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!
