subtract value by date

Question

How to subtract a set amount to be deducted on a certain date, repeated monthly?

Like 3450-150 monthly on the 23rd of the month.

With a new amount each month, it's like 3300 -150, 3150 -150, and 3000 -150, so forth till the end.

Answer 1

Hello, I'm Quinn and here to help!

In addition to the previous answer, you can make the deduction flexible:

1. Put your starting amount in A1 (e.g., 3450).
2. Put your monthly deduction in B1 (e.g., 150).
3. In a new column, type this single formula to generate, say, 12 months automatically: =$A$1 - $B$1 * SEQUENCE(12,1,0,1)

It will produce a column like this. This is just an example, you can format it any way you want, and changing the value in B1 will automatically update all months.

Answer 2

To achieve a monthly deduction of a set amount on a specific date, such as subtracting 150 from an initial amount of 3450 on the 23rd of each month, you can set up a simple formula in Excel. Here’s how you can do it:

1. Start with your initial amount in a cell (e.g., A1 = 3450).
2. In the next cell (A2), you can use a formula to calculate the new amount after the first deduction:

      =A1 - 150
   

3. Drag the formula down to fill the cells below for as many months as you need. Excel will automatically adjust the formula for each subsequent month.
4. To automate the date check, you can use the IF function to only apply the deduction on the 23rd of each month. For example:

      =IF(DAY(TODAY())=23, A1 - 150, A1)
   

   This formula checks if today is the 23rd. If it is, it subtracts 150 from the previous amount; if not, it keeps the previous amount.
5. Repeat the formula for each month to continue the deductions.

This setup allows you to track the deductions monthly and see the updated amounts automatically.