Excel question

[Chicago Jake]

Hey, are any of you geeky bastards out there Excel gurus?

I'm trying to program a standard deviation of a range of cells, but not necessarily the ENTIRE range of cells.

If I knew in advance that I had, say, ten data points, I'd say

=STDEV(A1:A10) or something like that.

But.... I only know that I have UP TO 50 data points, with the actual number of data points entered by the user. I want to use only the first X of those fifty, where X is entered by the user. (If I use all fifty, the zeros get averaged in and screw things up) I tried plugging the X into the formula given above and just got an error.

I'm digging through the documentation now, but figured I'd try the lazy way out first. Thanks!.......Jake

[♥ COVID-19♥]

Have you tried the other formulas -- e.g., STDEVA, STDEVP, STDEVPA? They might suit you better.

-- BB (who used to do QA on an Excel - Based plug-in and is furious that you made him think about work on his day off)

[Chicago Jake]

Thanks, I'll check it out. Hey, at least you GET a day off. When you are self-employed, there's no such thing as a paid day off.

[Chicago Jake]

Hmmmm, no help there. The P and A option are only to account for biased vs. unbiased (that is, n vs. n-1) formulas, and whether logical and text input is allowed. No help for the variable range.

[♥ COVID-19♥]

I don't know if this helps (or even if I properly understood your original question, for that matter), but the Forumla Wizard in Excel can help you to include/exclude certain rows. It's hard for me to describe, though, but you can enter in a range of cells while excluding another range (even if that range is only a single cell).

[Ardbeg... innit]

Jake

Here is a Quattro Pro function that sounds like what you are looking for

@purestd - Population Standard Deviation, Ignoring Labels and Blanks
Syntax
@purestd(List)


List One or more numeric or string values, cell addresses, and cell references or names, separated by commas.


@purestd returns the population standard deviation (square root of the population variance) of numeric values in a list, ignoring blank cells and labels. Compare this to @std, which returns the population standard deviation of all values in a list.

@purestds and @stds return sample standard deviation.

@std and @purestd use the n method to calculate standard deviation of population data. This method assumes that the sample reflects the entire population. If the sample is small, the standard deviation is biased because of sampling errors, so @stds or @purestds should be used instead.

@functions that ignore blank cells and labels are extremely important when using a spreadsheet for statistical analysis.

Examples

A
1 Grades
2 4.0
3 3.4
4 3.7
5 3.6


@purestd(A1..A6) = 0.216506, ignoring the label and the blank

@std(A1..A6) = 1.4827, because the argument includes the label, which to @std = 0

@std(A2..A6) = 0.216506, excluding the label from the argument

@purestds(A1..A6) = 0.25, the sample standard deviation

[Chicago Jake]

I should point out that I am creating a re-usable template. I don't know in advance what the range of cells is going to be for a particular application. Hence my range must be defined in terms of a user-set variable.

I'll check out the suggestions, thanks......Jake

[♥ COVID-19♥]

The user can always edit the formula field in Excel in order to re-define the value range; I would think that would be acceptable for a bunch of engineering students, but I don't know how you plan on applying this. If this is for one of your business clients, however, this suggestion may not be viable.

Also, keep in mind that if the user does an Insert Row in Excel, the formula should automatically get re-defined for that range, to include the inserted row -- hence, you would not necessarily need to add N number of rows to have a total of 50 rows that would have a value of zero which would throw off the calc of your STDEV.

[Chicago Jake]

Yes, I agree with those concepts, but I am trying to avoid requiring the user to edit a formula or insert rows. I'm giving him a field of fifty rows (which should be plenty; this is for an SPC analysis), and only asking him to enter the number of rows of data he wants to use, and then populating those rows.

The rows he does not fill in remain blank, which is not a problem. But the sums and averages of the rows, even if blank, go to a non-blank numeric zero. The zeros are not ignored in subsequent calculations, so I want to limit the range of those subsequent calculations.

If this could be in FORTRAN, I'd have had it done before my first cup of coffee....

[♥ COVID-19♥]

I tried Googling Standard Deviation Excel.

Maybe one of these sites might give you an idea how it could work for you.

[Chicago Jake]

bukkakeboy said:

I tried Googling Standard Deviation Excel.

Maybe one of these sites might give you an idea how it could work for you.

I was just thinking along those lines myself. I could just program the Std. Deviation formula myself and not use their bloody worksheet function.

[Ardbeg... innit]

admin said:

If this could be in FORTRAN, I'd have had it done before my first cup of coffee....

...less than 20 lines of code, including the I/O

[Chicago Jake]

Eureka! I have found it! It is the "Indirect" function.

The Indirect function is embedded in the desired function, and it refers to the location stored in the referenced location, rather than the number stored in the location. Beautiful! In my case, the formula comes out to:

=STDEV(INDIRECT("o27:o"&Q18))

where cell Q18 stores the number of the final cell of the range that begins at o27.

Thanks for the help, gang....Jake

[Ardbeg... innit]

where do we invoice?

[Chicago Jake]

Just send a check to Accounts Receivable and I'll take care of it.

[Ardbeg... innit]

... straight into the squabbulator