I've built a function in a Google Sheets that takes pages of data and passes them through to pull metrics from the datasets (which are based on pivot tables of extracted data). All data is pulled programatically, formatted the same way, and named by standardized conventions. I want the standard deviation of all values in the set that are above 100 and in most cases the function I've built works:
=STDEV((IF(INDIRECT($A7&"!B:B")>100,INDIRECT($A7&"!B:B"),""))
However with some smaller datasets STDEV starts throwing errors representative of not being passed enough arguments. I've tried debugging by pulling out pieces, eliminating thresholds, and trying other varieties of STDEV (STDEVA gives me a DIV/0 error, STDEVP and STDEVPA return 0 as the standard deviation) and when I pull out that IF statement it looks like it's returning FALSE as though there's no data in the set that fits the criteria. Except, when I lower the thresholds to >0 or eliminate the threshold entirely it still doesn't work and I know that there are 4+ values in all erroring datasets that are >100. In fact, the same call is summing to non-zero in the column right next to it. What's more, the function works everywhere else but these datasets.
What gives?
For extra info here's a viewable link to the sheet:
https://docs.google.com/spreadsheets/d/1b_456W9UlkuIc6W_FjmFwgycY1xAUkD_W9aMSxG0N6o/edit?usp=sharing
And this is the error the STDEV is throwing:
"Function STDEV parameter 1 expects number values. But '' is a text and cannot be coerced to a number."
Halp
