AVERAGE and STDEV functions with logic

[t-rung]

Good Morning:

I am going crazy trying to determine what is wrong with my formula. It
seems simple but for some reason I cannot get it to produce and display
correctly. Here is the issue:

I am trying two calculations based upon a range of numbers that are in
a column (lets just say A2:A4). I want to compute the average and
standard deviation using the AVERAGE function in excel and the STDEV
function in excel. Here is where it gets a little tricky. If there
are any "zero" values in the data I want the formula to ignore those
values and still produce a result with the remaining relevant
information. So lets say that the data in the column looks like this:


COLUMN
A
-----------------------
ROW(2) 10
-----------------------
ROW(3) 5
-----------------------
ROW(4) 0
-----------------------

I want my AVERAGE function to take determine that the only data it will
use to take the average are in rows 2 and 3 since row 4 has a zero
value. And the same with my STDEV function. Now I believe that I
found the correct way to use the function with logical IF functions but
I keep getting a #VALUE! result in the result cell. When I look in
detail I am getting the correct result but it shows up as #VALUE! in
the cell. WHY!!!! My STDEV function looks like this:

=STDEV(IF(G2:G4<>0,G2:G4,"")) I see the formula result of 3.536 in the
gray formula box which is correct but the cell still returns the
#VALUE!. Please help.

I have the exact same problem with the AVERAGE function. The gray
formula box displays the correct result but the cell displays the
#VALUE!. This formula looks like:

=AVERAGE(IF(D24<>0,D24,""))

I think maybe I am just missing something very small but I cannot
figure out what. Please help.

Regards to anyone who can,
Tom

 

[spog00]

you are using an array within an if function. Instead of entering the
forumla with enter, use ctrl+shift+enter.

Try that, and if it dstill not workin, come back!!