View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.misc
cervenyc
 
Posts: n/a
Default trying to make a formula for percent if greater than a value

I guess I'm not clear what you mean about the array... please see below:

"SteveG" wrote:


I think I got this straight. In one instance, you want to count the
number of cells in a range that are greater than zero and divide it by
the total number of cells in the range.

=COUNTIF(A1:A8,"0")/SUM(COUNTBLANK(A1:A8)+COUNTIF(A1:A8,"0"))

A1:A8 being the range.

Then you also want to average the values from the range A1:A8 if they
are greater than zero?

=AVERAGE(IF(A1:A80,A1:A8,FALSE))

This is an array so you need to commit with Ctrl-Shift-Enter which
results in the formula being enclosed in curly brackets like.

{=AVERAGE(IF(A1:A80,A1:A8,FALSE))}


As I mentioned in my other post, I don't have A1:A8, rather I have them
across rows and not in ever cell. Ex/ F1, I1, L1, O1, R1, U1. I've come up
with (based on your suggestions) a way to find a count of the number of cells
in which the value is greater than zero, and divide this over the total
number of cells surveyed. But now I want to take the average of the values in
those cells.

Then to make it more complicated (as long as you are helping) if the values
in F1, I1, L1,O1, R1, U1 for example is greater than 0, then I want the
average of values in adjacent cells. So for example lets say I,L,O had values
greater than 0, then I want the average of the contents in J12,M12,P12.
Make sense? Thanks.




HTH


Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=499913