View Single Post
  #7   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

Okay, Thank you very much for your help.

I have used a combination of the above formulas to make this basic example:
=(COUNTIF(A12,"0")+COUNTIF(b12,"0")+COUNTIF(C12, "0")+COUNTIF(D12,"0")+etc)/COUNT(A12,B12,C12,D12,ETC)

This seems to have solved my basic problem. The challenge will be when I
have a value such as "." instead of 0 and I want it to drop that value. So
instead of evaluating 6 cells, I might only want it to evaluate 5. I'll mess
around with this and see what I come up with... Thanks again!


"Peo Sjoblom" wrote:

Best would be to link the cells to a continuous range otherwise if you only
have 4 cells you can just add them like

=COUNTIF(A1,"0")+COUNTIF(A3,"0")+etc

if you have many cells you can select them using ctrl and mouse click and
then give them a name like MyRange (insertnamedefine) and use


=SUMPRODUCT(--(LARGE((MyRange),ROW(INDIRECT("1:"&COUNT(MyRange)) ))0))


--

Regards,

Peo Sjoblom



"cervenyc" wrote in message
...
The countif function doesn't seem to work when I'm not applying it to a
range. Well, I am applying it to a range, but it is not a continuous

range.
i.e. not A1:A8, but rather A1,A3,A6,A8. How do I make the formula work
without a continuous range? Thanks.


"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))}

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