Statistics on an Excel Data Table

This might be what you want:

https://exceljet.net/excel-functions/excel-isnumber-function

interesting website.

I guess the second part of my question: is the standard deviation being inflated by adding in a bunch of terms that tricks Excel into thinking blank cells are zero?

I am sure ISNUMBER (0) = true. I am pretty sure ISNUMBER(_) = false.

The trick would be incorporating that into a calculation of standard deviation where the number of terms is over 100.

I, of course, have the option of rendering the data a different way, into a contiguous column pure in number values, with no blanks, zeroes, or text.

Note that the standard deviation calculations for data with gaps and with no gaps are identical, but the data that is zero-filled presents a huge problem.

Thanks, Rixter for making me focus better.

Here is a forum question that might help:

https://www.mrexcel.com/forum/excel-questions/479443-standard-deviation-formula-will-ignore-0-help.html

Ok, I see that If(,,) can be used within stdev.p function.

also saw the one with isnumber (range).

Stuffing blank entries would skew the data. If there isn't any real data value then nothing should be substituted into that spot. Zero stuffing is used in DSP to change the sample rate of a signal which changes the energy content. The STNDEV in any data set that is zero stuffed would be similar.

Write a short macro to remove blanks.

Should have clarified my last statement if "you" read the whole comment more clearly.

The STNDEV in any data set that is stuffed with erroneous data will be modified to fit that new data set. As I said, the energy content (STD) would be wrong.

Not really. That method will artificially lower the standard deviation by biasing zero values to the average.

I would suggest making a value copy of the data table, breaking it into individual two column tables that include the dates/times and the individual data elements and then sort the two column tables based on the data value. This will collapse the data with the zeroes sorted to the end of the column. Then you can run average and standard deviations on the columns of non-zero data. If you wanted to automate it a bit further, you could add two columns alongside the sorted date and value column and do an if/then formula to calculate the average from the top of the data column to the present level in the third column and the standard deviation in the fourth column.

The last average and standard deviation in the column would be the summary for the data in the column.

Seems like you should also take additional specific care to qualify that each (possible column entry) is an actual number...

(i.e.: something like: ( if $@#is > 1, then count-value is 1 ) along with ( if count ="1" then sum($@) = (c+1), else, not) and then Stat functions are each a function of a totally numeric (C), rather than a possibly mixed (n), to start with, in order to eliminate such things as dates being treated as numbers, etc.)...

hopefully, helpfully, good luck, in any case...

It does seem I used the correct option. This works as my time is somewhat limited that I would eschew calculation of the appropriate sums of data in a more manual way.