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:

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:


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

Regards to anyone who can,




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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question