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 )
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 )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.
