Forum Discussion
Monthly expenses and income - how to calculate formulas
I'm working on creating a monthly expense and income chart for personal reasons. Expenses are paid monthly, quarterly, semiannually, and yearly.
From a video I watched, I had the idea to use a Quantity and Frequency column. Would a formula would work for this? I'm guessing there should be a lot of "IF this" and "IF that" in the formula, but do not have a clue where to begin, nor am I a math genius. They also talked about using VLOOKUP, but the directions for that was beyond my knowledge level as to setting it up.
I just want to be able to type an expense amount and have Excel calculate the amount as a monthly figure.
- The monthly expenses in the Amount column should automatically be repeated in the Expenses column.
- The quarterly, semiannually, and yearly expenses in the Amount column should appear in the Expenses column as a monthly figure.
Or, if there is an easier way to get the same results, please let me know.
I don't see a way to upload my example .xlxs file to this post.
Microsoft® Excel® for Microsoft 365 MSO (Version 2401 Build 16.0.17231.20236) 64-bit
Windows 10
If I'm understanding your goal correctly with my solution, there are a few columns that can be removed. My recommendation is to table the data and use SWITCH to simplify the logic:
=SWITCH( [@Rate], "monthly", [@Amount] * 1, "bimonthly", [@Amount] * 6 / 12, "quarterly", [@Amount] * 4 / 12, "seminanually", [@Amount] * 2 / 12, "yearly", [@Amount] * 1 / 12, 0 )
10 Replies
I don't see a way to upload my example .xlxs file to this post.
If you still need more help, if the reply you got from HansVogelaar hasn't resolved all of your questions, you can post a copy of your example file on OneDrive or GoogleDrive, and then paste a link here that grants access to that file.
It doesn't look like Hans' suggestions will figure the calculations automatically in the way I was hoping. Or I am misunderstading. Using Hans' suggestion, I would have to type the formulas for each amount. Also, now I'm not sure of the "divided by" numbers.
I added some calculations to my document.
https://docs.google.com/spreadsheets/d/1GzpI-AjU9J2CDhIdWlC-4UsVakJcL37b/edit?usp=sharing&ouid=114161304586151066003&rtpof=true&sd=true
Regarding the Quantity and Frequency columns, I was hoping to create a formula that could do this, but I do not know how to create such a formula.
Thanks!
If I'm understanding your goal correctly with my solution, there are a few columns that can be removed. My recommendation is to table the data and use SWITCH to simplify the logic:
=SWITCH( [@Rate], "monthly", [@Amount] * 1, "bimonthly", [@Amount] * 6 / 12, "quarterly", [@Amount] * 4 / 12, "seminanually", [@Amount] * 2 / 12, "yearly", [@Amount] * 1 / 12, 0 )
The missing "Divide by" values in column F should be 2, 3 and 6, respectively.
The formula in B3 could be =A3/F3, or if you want to multiply with the quantity, =A3*C3/F3
