Nesting-Don't use Blanks inside formula
 

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.

Nesting-Don't use Blanks inside formula
 

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.

Nesting-Don't use Blanks inside formula
 

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.

Nesting-Don't use Blanks inside formula
 

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.

Nesting-Don't use Blanks inside formula
 

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.

Nesting-Don't use Blanks inside formula
 

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.

Nesting-Don't use Blanks inside formula
 

Angela,

Blanks are not zeroes, and Excel treats them differently. Which do you have?

HTH,
Bernie
MS Excel MVP


"Angela" wrote in message
...
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.

Nesting-Don't use Blanks inside formula
 

I have blanks.

"Bernie Deitrick" wrote:

Angela,

Blanks are not zeroes, and Excel treats them differently. Which do you have?

HTH,
Bernie
MS Excel MVP


"Angela" wrote in message
...
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.

Nesting-Don't use Blanks inside formula
 

Angela,

If you truly have blanks, then Excel will ignore them for all of the functions that you want to use.

=AVEDEV(IF(A1:Z1<"",A1:Z1)) - Array entered using Ctrl-Shift-Enter
=AVEDEV(A1:Z1)

Both return the same for me, no matter how many blanks, text strings, and "" values I add in.

HTH,
Bernie
MS Excel MVP


"Angela" wrote in message
...
I have blanks.

"Bernie Deitrick" wrote:

Angela,

Blanks are not zeroes, and Excel treats them differently. Which do you have?

HTH,
Bernie
MS Excel MVP


"Angela" wrote in message
...
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.

Nesting-Don't use Blanks inside formula
 

Bernie ~ It is working for me now. Thank you! I found out what i was doing
wrong. I was making changes to the array. If I make changes, i need to go
ahead and delete the formula and just re-enter it. Then the formula you both
gave me works perfectly. Thank you for all your help. :-)

"Bernie Deitrick" wrote:

Angela,

If you truly have blanks, then Excel will ignore them for all of the functions that you want to use.

=AVEDEV(IF(A1:Z1<"",A1:Z1)) - Array entered using Ctrl-Shift-Enter
=AVEDEV(A1:Z1)

Both return the same for me, no matter how many blanks, text strings, and "" values I add in.

HTH,
Bernie
MS Excel MVP


"Angela" wrote in message
...
I have blanks.

"Bernie Deitrick" wrote:

Angela,

Blanks are not zeroes, and Excel treats them differently. Which do you have?

HTH,
Bernie
MS Excel MVP


"Angela" wrote in message
...
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.