I want to calculate how much of a service is is split monthly

[Joanne_Georges]

I want to make sure the cost of the services is split according to the months it's meant to run. For example, a Product is $16,500 and will run for 6 months (Start in Feb, End Jul), so Product Per Month is $2,750. I want to create a formula that puts a $2,750 in each field (image attached of my table). So in my mind the monthly formula should read: If Start is after 1/31/2023, than equal Product Spent Per Month. The formula that I have in each month is: 

{Product Per Month} and that's it.

I don't know how to make the formula for If, than in this case.

[AirBenderMarcus]

Update: Edited formula to convert string values back into numbers.

Is the question that you only want the price to populate on the months that are within that range?

Try this formula in each of your month fields where you replace the # with the number value of the month (Jan = 1, Feb = 2, etc. ) 

IF(AND(#>=MONTH({START}),#<=MONTH({END})), SUM({Product Per Month}), SUM(""))

If you don't need the Product Per Month or Month field for anything else besides the calculation, we could even get rid of those fields entirely by baking the calculations into the month formulas (though the formulas would start to look a bit busy). 

[Joanne_Georges]

This is almost perfect! Thank you so much so far. I just need it to calculate as the sum of each month. So my old formula in March is set as currency, and provides the sum at the bottom. But this new formula won't format to currency and doesn't provide a sum.

[AirBenderMarcus]

Ahh! The formula has converted the number into a string (text). Wrap the {Product Per Month} and blank value in SUM functions to turn the strings back into a number.

IF(AND(#>=MONTH({START}),#<=MONTH({END})), SUM({Product Per Month}), SUM(""))

Edit: My original formula was incorrect. Updated to one that works.

[Joanne_Georges]

Okay perfect solution, but 1 more edit. Thank you again so much, you've been so helpful. 

So now, some products have been ongoing for more than a year. For example, a client for 24,000 start in Jun 2022 and will end in Jul 2023. How do I make sure the month's of Jan-Jul 2023 are adequately filled in the when the start happened in 2022?

[kuovonne]

I have several thoughts about this topic. Most of them involve revisiting the base schema. However, I don't know if you are open to a very different way of organizing your data.

Instead, I will recommend that you do not use SUM() in your formula. Instead, leave out the third parameter of the IF() function completely.

[AirBenderMarcus]

I've been lurking on these forums for a long time so I'm geeking out that the great @kuovonne responded to this!

Leaving off the last in the argument in the IF makes sense.

Why would you not recommend using SUM? Are there potential ramifications down the line?

[kuovonne]

SUM() should be used when you need to add numbers together. In this case, rather than using SUM() to turn text back into a number, you should identify why the formula was returning text in the first place and correct that. I cannot see the original formula, but I suspect it was because you included an empty string "" as the third parameter of your IF(), which is a very common mistake.

Unnecessary calculations can clutter up the formula making it harder to understand. Having a clean writing style and understanding how a formula determines its data type is essential to writing and maintaining more complex formulas.

Unnecessary calculations can also put an additional load on the servers which can slow down services. In a small base, a single extra SUM() probably won't have much effect. However, lots of inefficiencies across bases add up. It is better to practice clean code from the start.

If a formula doesn't give the result you want (either the actual value or the data type), figure out the underlying cause instead of putting a band-aid on top of the problem to cover it up.

[Joanne_Georges]

Hi! 

Thank you for the suggestions, but I'll be honest some of this has gone over my head a little. I'm open to any suggestions for reorganizing the data. My primary goal is to see how much of the product/service is attributed to each month. I'd like to eventually use the Airtable Interfaces to use the graphs something like the image below.

[kuovonne]

Since you are open to restructuring your base, but feel over your head, the next question is do you have budget? If you have budget, you could hire a consultant to show you how to restructure your data across multiple tables so that it will scale across multiple years and also produce the pretty graph that you want.

If you do not have budget, how much time are you able to spend into learning database architecture? Notice that in your current setup, as time goes by you need to add more fields--your table gets wider. Having your table get wider simply because time passes is usually a sign of a design that could be improved. A better design is where you add more records as time passes, not more fields.

There are various posts on these forums that talk about restructuring a base so tables get taller over time versus wider, but I have given up on the search on these forums.