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 )
See the attached workbook. I used Insert > Table to format the range as a table.
I now have my data in a table using Home > Format as Table and created headers.
I found the problem. Since my Rate column does not yet any semiannally amounts, I removed that from the Switch formula so it says:
=SWITCH([@Rate],"monthly",[@Amount]*1,"bimonthly",[@Amount]*6/12,"quarterly",[@Amount]*4/12,"yearly",[@Amount]*1/12,0)
It now shows all my Expenses correctly.
Patrick2788 and HansVogelaar - thanks much for all your help!
I found the problem. Since my Rate column does not yet any semiannally amounts, I removed that from the Switch formula so it says:
=SWITCH([@Rate],"monthly",[@Amount]*1,"bimonthly",[@Amount]*6/12,"quarterly",[@Amount]*4/12,"yearly",[@Amount]*1/12,0)
It now shows all my Expenses correctly.
Patrick2788 and HansVogelaar - thanks much for all your help!
- Glad to help. You're welcome!