Nesting-Don't use Blanks inside formula

[Guest]

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]

Hi!

Empty cells and cells that contain text are ignored.

You don't need to specifically exclude empty cells.

Biff

 

[Biff]

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

 

[Guest]

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

 

[Bernie Deitrick]

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

 

[Guest]

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]

Angela,

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

HTH,
Bernie
MS Excel MVP

 

[Guest]

I have blanks.

Angela,

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

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

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

 

[Guest]

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.