Weighted Summarization

I have a table that has, for several items, the cost and the current value of the item:

Item. Cost. Value. Multiple (formula = value/cost)
A. 10. 20. 2
B. 5. 15. 3
C. 1. 1. 1
D. 10. 5. 0.5

For this table I need a summary. For Cost & Value, i can use the Sum function and it works just fine. For the multiple, none of the preset aggregators work, as I would need a weighted-average aggregator. I would need to calculate the sum of value for all items and divide by the sum of cost for all values. averaging the ratios would generate incorrect results.

How should I do this?

@Oren_Pinsky

For more traditional spreadsheet use cases (rather than relational data), I frequently just throw it together as shown below. It’s not as presentable as having summaries inline with their source data, but such are the tradeoffs.:grinning:

1 Like

Hi @Oren_Pinsky :slight_smile:
Welcome to the Community :slight_smile:

The answer from @Ander works but if you prefer an approach a little bit more “coda-friendly” i can propose mine:

You have a first table that contains all your data
Below it you have a second table that calculate all your desired totals :slight_smile: (so then inside those column you can write formulas as advanced as you like :slight_smile:

Another option could be to use canvas formulas, you just write something like:

Cost & Value Multiple: =sum()/sum()

The final result can be formatted and styled :slight_smile:

The first solution can be better if you have also other dataset where you want to perform the same calculi, the second is if you dont :slight_smile:

Have a nice day,

Mario

1 Like

Many thanks @Mario and @Ander. I am using the table grouping feature, so I was hoping to be able to use the summarization features built-into the table. As this is apparently not possible - as there is no ‘weighted average’ function for group summaries (only sum, max, count, etc) I will experiment with your solutions!

Also, it would be great to suggest a feature request to coda so that they can implement the weighted average function as per above. How should I do it?

Thanks

1 Like

Happy to help @Oren_Pinsky :slight_smile:

To communicate with coda your ideas for new features you can create a new post here in the community in the suggestions category, or let’s wait for them here :slight_smile: @coda_team

column menu > summarize > list of options

A Custom formula option here would be powerful.:sunglasses: