I’m doing up a spreadsheet to allocate some pre-set monthly amounts. There’s several different categories, and a different amount is to be allocated to each category each month. Some months there is a payment out from one or more of the categories, but other months there’s no activity in a category so the amount will increase.
Is there a function that allows the pre-set amounts to be added automatically to each cell on the first of the month?
I’m having trouble visualizing your spreadsheet. Is there a column for each category that holds the values for more than one date or are you talking about a single cell for each category that will automatically change based on today’s date?
It sounds like you are looking for a decision that may be expressed as =if(Day(now()=1,25,0)+A1 if you were looking to add 25.00 to the value of A1 based on the date.
or if(Day(B1)=1,25,0) +A1 which could be used to test the date that isn’t today but may change within the spreadsheet.
If you are looking to adjust one cell based on today’s date I can’t see that accomplished with just a function. A macro would do that but even then you’d have to structure the macro to avoid any iteration issues that could compound the allocations.
The latter. For example, suppose I have three categories of expenses that fluctuate each month: heat for the office, gas for the delivery van, and office supplies. I know on average how much they cost over a year, so I want to allocate a set amount of money each month to those expenses. Some months I won’t use very much, others I’ll use a lot from a particular category. I want a set amount (1/12 of the average annual expense in each category) to be added automatically on the first day of the month. As I pay bills during the month, I’ll subtract the actual amount paid, which will vary. So it’s just two columns of 3 cells each, in this example:
Heat $100
Gas $50
Supplies $25