I have what I think is a pretty simple goal: to find the standard deviation of a set of numbers which is itself derived from two ranges. I feel I aught to be able to do something like this: =stdev(A1:A2 - B1:B2) But I can’t get this to work. It would save me a TON of space in my sheets, and some very inconvenient shuffling for revisions, if ranges could be operated on in a sort of hypothetical, no-printing-to-cells sense.
-Aidan
How about enter the formula with Ctrl+Shift+Enter,
{=STDEV(A1:A2-B1:B2)}
or wrapping the formula in the SUMPRODUCT() function?
=SUMPRODUCT(STDEV(A1:A2-B1:B2))
I got so close! In my testing, these solutions both (usually) work. Would you help me understand why the SUMPRODUCT function works in this case? It looks like a pretty different behavior than is described in the docs you kindly linked.
You can consider this a “dirty hack” or a simple trick. In this case, the SUMPRODUCT() function is used in the same sense as the ArrayFormula() function in Google Sheets - it replaces the “array formula”.
The parameters of SUMPRODUCT() are specified ForceArray in the relevant ODF documents.
See https://docs.oasis-open.org/office/OpenDocument/v1.3/cs02/part4-formula/OpenDocument-v1.3-cs02-part4-formula.pdf 6.3.4 and 6.16.64.