How Do I Make Numbers Subtract A Cell From A Cell Based On The Day Of The Month?

Question

Level 1

5 points

How Do I Make Numbers Subtract A Cell From A Cell Based On The Day Of The Month?

Hey guys, I created a monthly budget for myself in Numbers which I put all of my monthly expenses into and have it so that it will add them up and then subtract them from what I put in as my balance following my bi weekly check. So twice a month I add in that number and then it does the calculation to subtract the remaining expenses that still need to come out. So that I know the remainder. I also worked in tables for purchases and credit card payments.

But here is what I am wondering.

Can I make a line in my main table that will calculate my current balance by subtracting purchases and be intelligent enough to subtract expenses as they come through the month?

So that say on the 5th I have $1500 as the "actual balance" but then on the 6th lets say Disney plus comes out so it should now say $1492. Then do that for the whole month.

Preferably it would be based on the day of the month specifically because I create new tabs for each month. If it were based on the specific date then it would break every month, or I would have to manually change the dates every month.

I hope that makes sense.

Thanks guys.

Posted on Nov 22, 2021 12:29 PM

Reply

Answer 1

Barry

Level 9

60,023 points

Nov 23, 2021 12:13 PM in response to waspenatorJR

Here's an example using the formulas shown below the table..

D1: This calculates and displays Today's date. Entered as shown in cell D1.

You can choose a different format for the date, as desired.

The date can be entered manually in this cell. Doing so will replace the formula.

D2: The formula is entered in D2, then filled down to D17 (the last row containing a 'payment due' entry.

E2: Entered in E2 as shown, filled down as above.

Rows 18 and 19 are 'converted to Footer rows'. This makes possible the 'full column' references made to columns C and E in the formulas in E18 and E19 without creating a 'self reference error' in these two cells.

New rows added to the table will be inserted above these two Footer rows.

E18: Entered as shown in cell E18.

E19: This is the same formula as the one in E18, with "<=15" replaced by ">15"

Regards,

Barry

Reply

Answer 2

Barry

Level 9

60,023 points

Nov 22, 2021 7:26 PM in response to waspenatorJR

Answers may vary depending on which version number of Numbers you are using and the version of macOS you have installed.

You'll find these in the first items of the Apple menu (OS version) and the Numbers menu in Numbers (Numbers version).

A screen shot of your table might be useful, as it will provide a pattern from which the table answering your question could be constructed.

Have you looked at the Budget templates provided with Numbers?

Regards,

Barry

Reply

Answer 3

waspenatorJR Author

Level 1

5 points

Nov 23, 2021 6:35 AM in response to Barry

I am running Mac OS Monterey and Numbers is 11.2. The newest of everything.

Here is just one table. My idea was to to have the calculation occur here where if the day is on the day then it will carry over the number to the far left where it will be added at the bottom and then that number will be subtracted from the "actual balance" cell in another table.

If that makes sense.

Reply

Answer 4

waspenatorJR Author

Level 1

5 points

Nov 23, 2021 6:37 AM in response to waspenatorJR

My problems is having it know that from "x" day on the number should stay on the far right cell. I can get it to know that if it is the 1st of the month rent will go over to the other cell but on the 2nd it will become false again.

Reply

Answer 5

waspenatorJR Author

Level 1

5 points

Nov 23, 2021 1:52 PM in response to Barry

Granted I did have to change it a little to get it to work. Maybe I misunderstood your formula, but it does work right now.

If I put D2 as "Day(C2)<=Day(D$1)" it would not work. If I did "C2<=Day(D$1)" it would say that it could not compare a number and a date. But, if I did "C2<=D$1" it works.

Which I had the cells set as dates formatted as "5/1" for C and D columns.

Reply

Answer 6

Barry

Level 9

60,023 points

Nov 24, 2021 2:42 PM in response to waspenatorJR

"If I put D2 as "Day(C2)<=Day(D$1)" it would not work. If I did "C2<=Day(D$1)" it would say that it could not compare a number and a date. But, if I did "C2<=D$1" it works.

Which I had the cells set as dates formatted as "5/1" for C and D columns. "

My assumption on viewing your screen shot was that the values in column C were numbers, not Date and Time values displaying only the 'day'

DAY(cell) requires a date in the referenced cell, and extracts the day number of that date. The extracted value is a number.

In my assumption of number values in Column C was correct, Day(C2)<=Day(D$1) would fail and present an error triangle whose error message would be that DAY() requires a Date, but found a number (or words to that effect).

If the value in column C is a Date and Time value, displaying only the day or displaying the month and day, then C2<=Day(D$1) would return the error message you saw. Numbers can't compare a D&T (in C2) with a number (the result of the formula in D2).

Happy to see you've arrived at a working formula.

And thanks for the green checkmark.

Regards,

Barry

Reply

Answer 7

waspenatorJR Author

Level 1

5 points

Nov 23, 2021 1:47 PM in response to Barry

You are the best, Barry. I was able to make it work using your formulas!

Thank you so much!

Reply

How Do I Make Numbers Subtract A Cell From A Cell Based On The Day Of The Month?

Similar questions