When you create a Tables in Amplenote, we allow a handful of calculations to be undertaken on the cells in your table.
linkSpecifying a range
In lieu of specifying complex columns+rows ranges, Amplenote offers a simpler way to indicate which columns or row you want your calculation to include. Here's the formula:
For example, all of the following are valid ranges:
= sum(5 right) add up the five cells to the right of this cell
= mode(above) show the most commonly occurring number among the cells above this one
= median(3 below) what is the median (middle value) of the three rows below the current?
linkTransitive operations not supported in current implementation
If the range that you specify includes a formula, that result of the formula is ignored in the calculation. While most spreadsheets allow you to aggregate formulas, we opted to skip it until we allow specifying a range of cells.
Our rationale is that it's common to want to apply multiple operations to a single data set. For example, if you have a row that is the average temperate for each month, you might want to add up the mean and the median temperature for the year.
If transitive cells were allowed, one would need to be able to specify that the data range for the two right-most columns should not include the column(s) immediately to their left. In order to avoid the complexity of labeling every cell, we accept this limitation for the time being.
linkOperations supported
As of initial launch in late 2023, table formulas can include:
linkaverage or mean
(Sum of values) / (Count of values)
linkmax
The greatest value in range.
linkmedian
The middle value in range, e.g., [1, 2, 3, 4, 500] has a median of "3." Usually a better measure than "average" when evaluating statistical sets, since it mollifies the hazards of outlier data.
If the range has an even number of values, we take the average of the middle two values. For example, a set of values [0, 4, 10, 3000] would have a median of (4+10)/2 = 7.
linkmin
The lowest value in range.
linkmode
The most commonly occurring value in the range of values.
linkproduct
Multiply together all the values in range.
linksum
Add up all the values in range.
linkWhat's missing?
It's usually pretty easy for us to add more types of formulas to our set, so if you have a suggestion, please propose it on our voting boards. If you are a Founder subscriber, feel free to email Bill personally with your suggestion. 😅
linkFormatting results
Since we seek to avoid the complexity of traditional spreadsheet apps that require a user set the "Format" of their cell, we automatically infer certain types of formatting.
linkCurrency
If your input values include currency symbols like $ or €, the result of the formula will include the same denomination that your value(s) did.
linkNumeric separators
Different countries have different conventions for how commas and periods are used in long or precise numbers. We will follow your load on whether to use separators in the formula result. If your input values include commas, so too will the result.
Likewise, the formula result follows your lead on how much numeric precision should be used. If your input values max out at one decimal point, the formula result will also stop at one decimal. In general, we don't show more than three digits of decimal precision.
