I need a workaround for calculating a weighted average based on two columns of data

Hi! I am building a database for my employees to manage their client caseloads and I have two columns of data: (1) Fee per session that client pays and (2) # of Sessions/Month. All of our clients pay varying rates and may have different frequencies for meeting with their provider. For example, I may have one client who pays $200/session and meets 4 times a month and another client who pays $150/session and meets 2 times a month.

I need a workaround to be able to calculate a weighted average for the session fees (to account for the varying frequencies). That way we can capture overall, across a whole caseload, how much revenue a person is bringing in per session.

This is how far I’ve gotten:

  • Instead of Sessions/Month, I converted those numbers to Sessions/Week (so 4 times a month would equal 1 time a week, 2 times a month would equal 0.5 times a week). This would now be considered the “Weight” of each session fee.
  • I created a Formula column that calculates the Session Fee * Weight (e.g. $200 * 0.5 times/week = $100)
  • Now the sum of this Formula column divided by the sum of the Sessions/Week column does get me the weighted average I’m looking for. I would LOVE for this number to display somewhere easily visible in Monday so I don’t have to do a manual calculation outside of Monday.

Hoping someone has found some workaround for these mathematical calculations that would be quite simple in a spreadsheet.

Hello @laurel.meng ,

Welcome to the monday community! :muscle:

To my understanding, you have figured out how to calculate the metrics you need with the formulas you created.

You want to see your data gathered in one place and easily draw conclusions from it, so I would suggest looking into widgets and specifically the chart widget.

Let me know if I am missing something, and I would be glad to assist more!

Giannis, Implementation Consultant at thespelas.com

Hi Giannis,

Yes I have looked into the widget but I’m not sure that it would be able to do the calculations I am looking for. I need the widget to display the weighted average of revenue collected per session (weighted by the frequency that those sessions occur per month). Is there a widget that can do this math for me? I see that the numbers widget can capture a simple average but I need a weighted average.

Hmm for a weighted average I do not believe this can be done via widget unfortunately.