Forum Discussion
Solved
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 Q...
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