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

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

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.

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.

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

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?

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.

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?

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.

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.

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.

Hey @kuovonne. How do you know so much about how resource-intensive certain functions are? That is something I definitely want to understand better so I can better optimize my bases.

My comments about resource-intensive formulas are based on 

- statements from Airtable about how frequently time-based functions update
- personal observations of update times for rollups with many values
- comments from previous clients regarding base performance
- a study that a third party vendor published about API speeds across different base configurations (I don't recall the exact link.)
- observations of processing times of inefficient scripts (not formula calculations, but a similar idea)

The exact amount of processing required by any one function is tiny. It is how these add up that makes an impact. The care we put into our formulas/code now affects not only that code, but also develops habits that affect how we write future code, and affects others who see our code and copy the techniques. What happens if AI learns based on code examples and many of those code examples have smelly code? We have a duty to others and our future selves to write the cleanest code we can.

This is not to say that there is no place for quick/sloppy code. I have certainly written my share of stinky code. Some of it is still floating around the internet. It was the best code I could write at the time. But I still strive to write better code. I suspect that years from now, I will look at the code I write today and have better ways to write it.

Sorry to come back a year later. I'm still having an issue with the same table. My team is small and sorta sloppy already. So there are no funds for a consultant, and there is little room for a restructuing of how things are done. So, any notes would be greatly appreciated.

Hi again. If you're still struggling a year later then it sounds to me like you can't afford NOT to get a consultant. I'm happy to provide some more hands-on help through my consultancy, but as kuovonne originally suggested, you'll likely want to update your base structure. All the month splits should be its own record instead of an individual field because as I'm sure you've realized by now, maintaining new fields every month isn't all that scalable.

I completely agree, but I dont have that sort of authority to change the way infromation/clients are brought into the business. And I want to clarify, I can change the airtable "base" sure but this is how the information we have is preferred to be read by management. I'm just trying to make it easier for colleagues to input that information.

Either way, I don't understand what others have said about changing the base. I'm not understanding how else to present this information.

If you have a Client record that links to multiple Month records, you can generate the graphs in an interface like how you wanted in your original post. It will just require creating a new table. 

If the bigger concern is making it easier for your colleagues to input that information, then perhaps creating a custom interface for data entry might serve you as well!

I offer free consultations. I think I can probably get you on the right track during that session if you'd like.

yes, if you have the ability to quickly go over some options.