Standard Deviation in a log with a running total

To make it a little more complicated, I would like to make sure it uses prior months (other tabs) and if possible other years (in other worksheets).

I think this sort of task is much easier if you store your data in a good database with all of the data for all months and years in a single place. It is then much easier to calculate average and standard deviation (and other statistics) for all or subsets of the data.

Would you be allowed to make a good database for this task?

A spreadsheet can serve as a database just fine, as long as the database is small enough to fit in a single tab. Though a dedicated database application like Access will do better for larger datasets or more complex database queries. A database of daily records for 10 years would have about 3700 records. I would expect a spreadsheet could handle a database that size just fine. Then you can use pivot tables, functionIFS() functions, or function(IF(...)) functions to get summary data from the database.

My primary work in spreadsheets doesn't involve database type work, so I'm not much help at designing and running them. But let us know if you need some help exploring possibilities, because we have many users here who are very comfortable with designing and using spreadsheet databases.

It's not clear to me exactly what you want to calculate for a "running standard deviation." With a database in a single tab, a running standard deviation from the beginning of the database to the current record would be as simple as =STDEV(C$2:C2) entered into an adjacent column to the database and copied down [note the mix of relative and absolute references]. Average over the same period would be similar =AVERAGE(C$2:C2). One standard deviation below average is found by subtracting the standard deviation from the average. One standard deviation above is found by adding. From there, it's a relatively simple IF() test to see if the value in column C of a given record is outside of average+/-1*stdev.

Would something like that work for you?

As explained in the help files (stdev.p help file for example https://support.microsoft.com/en-us/...7-4f4e7462f285 ), Excel's average and standard deviation functions ignore empty cells or cells with non-numeric data.

Automatic updating -- depends on how you set it up. If you set it up right, it will automatically update. The usual answer here is to use a "structured table." I never use them, so I don't know how they work, and I'm not sure how well it works when you have these kinds of mixed absolute/relative reference situations. If it works correctly, a structured table is the easiest way to have the table with its formulas extend as you append data to the database.

ARRAY formulas are working.
SD

Formula:

Please Login or Register  to view this content.

AVERAGE

Formula:

Please Login or Register  to view this content.

Automatically adds rows when data added.
To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

If it is ok, what is the max deviation to be for CF.