AVERAGE and STDEV functions with logic

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(D2:D4<>0,D2:D4,""))

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

Regards to anyone who can,
Tom

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!!