Forum Discussion
Monthly expenses and income - how to calculate formulas
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 )
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
)
That works really well in your example and is exactly what I was looking for! Thanks!
But I can't get it to work in my actual live document where "Amount" is in column D instead of column A in the example. However, I don't see in your switch formula where it refers to a specific column. Am I missing something?
I pasted your formula below into my Expenses column, but it doesn't calculate an expense. I made sure that every word is spelled exactly as in your example, and in the same lower case for the words in the Rate column.
=SWITCH([@Rate],"monthly",[@Amount]*1,"bimonthly",[@Amount]*6/12,"quarterly",[@Amount]*4/12,"seminanually",[@Amount]*2/12,"yearly",[@Amount]*1/12,0)
I wonder what I am doing wrong.
- Did you table your data?
I missed that in your email, and I'm not sure what that means. Is it Home > Format as Table?