automatically update cell value each month

mrwatt

New Member
Joined
Jan 14, 2021
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
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
Value title 1200
Value title 2300
Value title 3400
Value title 4500

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 1175
Value title 2275
Value title 3375
Value title 4475

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 10
Value title 266.77
Value title 3166.77
Value title 4266.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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,555
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,126,939
Messages
5,621,725
Members
415,853
Latest member
Newlife72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top