I have a complex formula requirement
I have any number of cells/rows in a column (in this example 4 but it could be 10,24 or just 1)
For example
These values are up to date and current. However, next month they will be reduced by a figure from another cell. Let's say this figure is 100. This 100 will be divide by the number of cells/rows there are, so in this case, that's 4. So each value would be subtracted by 25 (100/4). Below will be the new values:
All well and good, I have the formula that can do this one time. The complexity starts because I want these values updating automatically every month and when one of the figures reaches 0 it is discounted (no longer included) and what was a division across four becomes a division across 3.
The other challenge of this is, say the figure from the other cell is 800 (instead of 100), using the table above, that means the 175 value would reach 0 but there would be 25 left over. This 25 would then need to be distributed evenly and subtracted from across the other 3. Demonstrated below:
I hope this makes sense and hope someone might be able to help.
I think I may be asking a little too much though :/
Thanks
I have any number of cells/rows in a column (in this example 4 but it could be 10,24 or just 1)
For example
| Value title 1 | 200 |
| Value title 2 | 300 |
| Value title 3 | 400 |
| Value title 4 | 500 |
These values are up to date and current. However, next month they will be reduced by a figure from another cell. Let's say this figure is 100. This 100 will be divide by the number of cells/rows there are, so in this case, that's 4. So each value would be subtracted by 25 (100/4). Below will be the new values:
| Value title 1 | 175 |
| Value title 2 | 275 |
| Value title 3 | 375 |
| Value title 4 | 475 |
All well and good, I have the formula that can do this one time. The complexity starts because I want these values updating automatically every month and when one of the figures reaches 0 it is discounted (no longer included) and what was a division across four becomes a division across 3.
The other challenge of this is, say the figure from the other cell is 800 (instead of 100), using the table above, that means the 175 value would reach 0 but there would be 25 left over. This 25 would then need to be distributed evenly and subtracted from across the other 3. Demonstrated below:
| Value title 1 | 0 |
| Value title 2 | 66.77 |
| Value title 3 | 166.77 |
| Value title 4 | 266.77 |
I hope this makes sense and hope someone might be able to help.
I think I may be asking a little too much though :/
Thanks
