How to determine the number of cells in a range that are not empty?

[Rnkhch]

Hello,

I do a lot of calculations involving ranges, but I realized I get erroneous results depending on whether all or some of the cells in the range contain numbers. Here is a hypothetical example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]STDEV[/TD]
[TD]STDEV/n[/TD]
[/TR]
[TR]
[TD]83[/TD]
[TD]78[/TD]
[TD]96[/TD]
[TD]92[/TD]
[TD]91[/TD]
[TD]85[/TD]
[TD]90[/TD]
[TD]6.148944935<strike></strike>[/TD]
[TD]<strike></strike>0.878420705<strike></strike>[/TD]
[/TR]
[TR]
[TD]79[/TD]
[TD]70[/TD]
[TD]82[/TD]
[TD]90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8.261355821<strike></strike>[/TD]
[TD]<strike></strike><strike></strike>1.180193689<strike></strike>[/TD]
[/TR]
[TR]
[TD]67[/TD]
[TD]79[/TD]
[TD]80[/TD]
[TD]78[/TD]
[TD]83[/TD]
[TD]76[/TD]
[TD][/TD]
[TD]5.492419018<strike></strike>[/TD]
[TD]<strike></strike>0.784631288<strike></strike>[/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Here I divide the STDEV numbers by n=7 (which is the case for the first row), but n is not 7 in all rows. So, how could I write a formula that would know what the n is for each row (ideally without the need for VBA )?

Thanks a lot for any input!

And Happy New Year

 

[jtakw]

Hi,

How about:

Book1
	A	B	C	D	E	F	G	H	I
1	83	78	96	92	91	85	90	6.148945	0.878421
2	79	70	82	90				8.261356	2.065339
3	67	79	80	78	83	76		5.492419	0.915403
Sheet441

Cell Formulas
Range	Formula
H1	=STDEV(A1:G1)
I1	=STDEV(A1:G1)/COUNT(A1:G1)

 

[Rnkhch]

Awesome!! Thank you!!!

 

[jtakw]

You're welcome.