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.
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.
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 (so then inside those column you can write formulas as advanced as you like
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
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
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?
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 @coda-team
