Hi. I am currently using the STDEV formula. Is there a way, that i can make
the formula use data from nonblank cells, only. For example: A1, A2, A5 will
have numbers listed. A3 & A4 are blank. Can i get the formula to use only
A1, A2, & A5?
Any help would be greatly appreciated.

Hi!

Empty cells and cells that contain text are ignored.

You don't need to specifically exclude empty cells.

Biff

"Angela" wrote in message
...
Hi. I am currently using the STDEV formula. Is there a way, that i can
make
the formula use data from nonblank cells, only. For example: A1, A2, A5
will
have numbers listed. A3 & A4 are blank. Can i get the formula to use
only
A1, A2, & A5?
Any help would be greatly appreciated.

I hit send before I was done!

This array entered formula specifically excludes empty cells:

=STDEV(IF(A1:A5<"",A1:A5))

But it returns the exact same result as:

=STDEV(A1:A5)

Biff

"Biff" wrote in message
...
Hi!

Empty cells and cells that contain text are ignored.

You don't need to specifically exclude empty cells.

Biff

"Angela" wrote in message
...
Hi. I am currently using the STDEV formula. Is there a way, that i can
make
the formula use data from nonblank cells, only. For example: A1, A2, A5
will
have numbers listed. A3 & A4 are blank. Can i get the formula to use
only
A1, A2, & A5?
Any help would be greatly appreciated.

Biff, What if i need the same result as below, but was using the formula
=AVEDEV, =AVERAGE & =MEDIAN. Can i make it do the same thing? I tried using
the IF statement you wrote below on them, and it didn't work. It does work
on the =STDEV, though. Thank you in advance for all your help. You really
know your formulas. Thanks :-)

"Biff" wrote:

I hit send before I was done!

This array entered formula specifically excludes empty cells:

=STDEV(IF(A1:A5<"",A1:A5))

But it returns the exact same result as:

=STDEV(A1:A5)

Biff

"Biff" wrote in message
...
Hi!

Empty cells and cells that contain text are ignored.

You don't need to specifically exclude empty cells.

Biff

"Angela" wrote in message
...
Hi. I am currently using the STDEV formula. Is there a way, that i can
make
the formula use data from nonblank cells, only. For example: A1, A2, A5
will
have numbers listed. A3 & A4 are blank. Can i get the formula to use
only
A1, A2, & A5?
Any help would be greatly appreciated.

Angela,

All those functions ignore blanks automatically.

With the

=STDEV(IF(A1:A5<"",A1:A5))

method, you need to use Ctrl-Shift-Enter rather than just Enter. That's what Biff meant by Array
formula....

HTH,
Bernie
MS Excel MVP


"Angela" wrote in message
...
Biff, What if i need the same result as below, but was using the formula
=AVEDEV, =AVERAGE & =MEDIAN. Can i make it do the same thing? I tried using
the IF statement you wrote below on them, and it didn't work. It does work
on the =STDEV, though. Thank you in advance for all your help. You really
know your formulas. Thanks :-)

"Biff" wrote:

I hit send before I was done!

This array entered formula specifically excludes empty cells:

=STDEV(IF(A1:A5<"",A1:A5))

But it returns the exact same result as:

=STDEV(A1:A5)

Biff

"Biff" wrote in message
...
Hi!

Empty cells and cells that contain text are ignored.

You don't need to specifically exclude empty cells.

Biff

"Angela" wrote in message
...
Hi. I am currently using the STDEV formula. Is there a way, that i can
make
the formula use data from nonblank cells, only. For example: A1, A2, A5
will
have numbers listed. A3 & A4 are blank. Can i get the formula to use
only
A1, A2, & A5?
Any help would be greatly appreciated.

Bernie, Please don't hate me. But I must be doing something wrong because it
isn't coming up with the same thing.
Example:
A1=1, B1=0, C1=2, D1=0, E1=3, D1=0, E1=4, F1:Z1=0
If using the =AVEDEV; =AVERAGE; =MEDIAN, it comes up with a different answer
than if i were to use the =AVEDEV(IF(A1:Z1<"",A1:Z1)).
It sounds weird, but it actually works until i enter data on the 4th cell.
Am i doing something wrong?

"Bernie Deitrick" wrote:

Angela,

All those functions ignore blanks automatically.

With the

=STDEV(IF(A1:A5<"",A1:A5))

method, you need to use Ctrl-Shift-Enter rather than just Enter. That's what Biff meant by Array
formula....

HTH,
Bernie
MS Excel MVP


"Angela" wrote in message
...
Biff, What if i need the same result as below, but was using the formula
=AVEDEV, =AVERAGE & =MEDIAN. Can i make it do the same thing? I tried using
the IF statement you wrote below on them, and it didn't work. It does work
on the =STDEV, though. Thank you in advance for all your help. You really
know your formulas. Thanks :-)

"Biff" wrote:

I hit send before I was done!

This array entered formula specifically excludes empty cells:

=STDEV(IF(A1:A5<"",A1:A5))

But it returns the exact same result as:

=STDEV(A1:A5)

Biff

"Biff" wrote in message
...
Hi!

Empty cells and cells that contain text are ignored.

You don't need to specifically exclude empty cells.

Biff

"Angela" wrote in message
...
Hi. I am currently using the STDEV formula. Is there a way, that i can
make
the formula use data from nonblank cells, only. For example: A1, A2, A5
will
have numbers listed. A3 & A4 are blank. Can i get the formula to use
only
A1, A2, & A5?
Any help would be greatly appreciated.