– Background –

We work with non-linear data that has to be normalized for basic math functions to work. (Think of sound or earthquakes). For example, the total energy of two speakers playing a sound at 80 Decibels each is ~83dB (not 160dB). This is because we have to normalize the data before we can add values and then convert back to decibels after.

    Total Level = 10 * LOG10( 10^(80dB/10) + 10^(80dB/10) )

– Problem –

Because of this normalization, the built-in AVG or SUM will not work in Sheets or Excel without manipulating the data before and after. We currently have to normalize all the data, perform a pivot on it, and separately convert it once the pivot is complete. Due to these extra steps, we are unable to see quick results or modify pivot tables.

We need a more efficient solution. Having a way to replace, substitute, override, or specify a custom function in Google Sheets or MS Excel would greatly simplify and reduce the extra steps involved in working with pivot tables in some engineering and science-based calculations.

Thoughts?