trying to make a formula for percent if greater than a value

I am trying to create a formula that will calculate percent survival. 
Basically I have data in cells and I want to make a fomula that tests if 
there is a value greater than 0. I want my answer to be not the values in the 
box, but the number of cells with data greater than zero divided by the total 
number of cells.

Then, I'd like to write another formula to average the values in those 
cells, but only if those cells have a value greater than zero. Can anyone 
help? 
0
cervenyc (6)
1/10/2006 9:25:03 PM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
521 Views

Similar Articles

[PageSpeed] 13

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:A8>0,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:A8>0,A1:A8,FALSE))}

HTH


Steve


-- 
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7571
View this thread: http://www.excelforum.com/showthread.php?threadid=499913

0
1/10/2006 9:57:09 PM
You need to use COUNT for the number of cells and COUNTIF for those 
containing values greater than zero.  i.e. =COUNT(A1:A10) will result in 10.  
If, say, three of those cells contain zero, =COUNTIF(A1:A10,">0") will result 
in 7 

If I understand the question correctly, your desired calculation would 
therefore be 7/10 or =COUNTIF(A1:A10,">0")/COUNT(A1:A10)

"cervenyc" wrote:

> I am trying to create a formula that will calculate percent survival. 
> Basically I have data in cells and I want to make a fomula that tests if 
> there is a value greater than 0. I want my answer to be not the values in the 
> box, but the number of cells with data greater than zero divided by the total 
> number of cells.
> 
> Then, I'd like to write another formula to average the values in those 
> cells, but only if those cells have a value greater than zero. Can anyone 
> help? 
0
bigwheel926 (227)
1/10/2006 10:02:03 PM
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:A8>0,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:A8>0,A1:A8,FALSE))}
> 
> HTH
> 
> 
> Steve
> 
> 
> -- 
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7571
> View this thread: http://www.excelforum.com/showthread.php?threadid=499913
> 
> 
0
cervenyc (6)
1/12/2006 2:21:03 PM
Try this.  If you always no that you will be evaluating 4 cells then fo
the percent of cells what are greater than zero.

=COUNT(A1,A3,A6,A8)/4


You can still use the AVERAGE and IF functions as shown in my firs
post to get the average of the values provided that cells A2, A4, A5 
A7 will not contain numeric values.


HTH

Stev

--
Steve
-----------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=757
View this thread: http://www.excelforum.com/showthread.php?threadid=49991

0
1/12/2006 2:46:00 PM
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 (insert>name>define) and use


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


-- 

Regards,

Peo Sjoblom



"cervenyc" <cervenyc@discussions.microsoft.com> wrote in message
news:25C5D550-F960-47D1-B2FB-3AF5F975BFF6@microsoft.com...
> 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:A8>0,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:A8>0,A1:A8,FALSE))}
> >
> > HTH
> >
> >
> > Steve
> >
> >
> > -- 
> > SteveG
> > ------------------------------------------------------------------------
> > SteveG's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=7571
> > View this thread:
http://www.excelforum.com/showthread.php?threadid=499913
> >
> >


0
terre081 (3244)
1/12/2006 2:46:44 PM
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 (insert>name>define) and use
> 
> 
> =SUMPRODUCT(--(LARGE((MyRange),ROW(INDIRECT("1:"&COUNT(MyRange))))>0))
> 
> 
> -- 
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
> 
> "cervenyc" <cervenyc@discussions.microsoft.com> wrote in message
> news:25C5D550-F960-47D1-B2FB-3AF5F975BFF6@microsoft.com...
> > 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:A8>0,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:A8>0,A1:A8,FALSE))}
> > >
> > > HTH
> > >
> > >
> > > Steve
> > >
> > >
> > > -- 
> > > SteveG
> > > ------------------------------------------------------------------------
> > > SteveG's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=7571
> > > View this thread:
> http://www.excelforum.com/showthread.php?threadid=499913
> > >
> > >
> 
> 
> 
0
cervenyc (6)
1/12/2006 3:16:03 PM
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:A8>0,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:A8>0,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?action=getinfo&userid=7571
> View this thread: http://www.excelforum.com/showthread.php?threadid=499913
> 
> 
0
cervenyc (6)
1/12/2006 3:46:06 PM
Enter the formula as you normally would.


=AVERAGE(IF(A1:A8>0,A1:A8,FALSE))

When you are done typing in the formula, rather than hitting the Enter
key, hit the Ctrl, Shift & Enter keys simultaneously.  That will create
the {} around the formula automatically.  Don't type them in yourself.

HTH

Steve


-- 
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7571
View this thread: http://www.excelforum.com/showthread.php?threadid=499913

0
1/12/2006 3:54:00 PM
I got it to work, but the formula averages a zero when the statement is 
false. How do I make a false statement render a blank cell, or to drop the 
value? If the value is 0, then I want it excluded, I only want to average 
values greater than 0. Thanks.



"SteveG" wrote:

> 
> Enter the formula as you normally would.
> 
> 
> =AVERAGE(IF(A1:A8>0,A1:A8,FALSE))
> 
> When you are done typing in the formula, rather than hitting the Enter
> key, hit the Ctrl, Shift & Enter keys simultaneously.  That will create
> the {} around the formula automatically.  Don't type them in yourself.
> 
> HTH
> 
> Steve
> 
> 
> -- 
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7571
> View this thread: http://www.excelforum.com/showthread.php?threadid=499913
> 
> 
0
cervenyc (6)
1/12/2006 4:31:43 PM
Thanks guys for all your help. For future seakers of the solution, here's 
what I've done...

=(COUNTIF(F12,">0")+COUNTIF(I12,">0")+COUNTIF(L12,">0")+COUNTIF(O12,">0")+COUNTIF(R12,">0")+COUNTIF(U12,">0"))/COUNT(F12,I12,L12,O12,R12,U12)


=SUM(IF(F12>0,F12,FALSE),IF(I12>0,I12,FALSE),IF(L12>0,L12,FALSE),IF(O12>0,O12,FALSE),IF(R12>0,R12,FALSE),IF(U12>0,U12,FALSE))/(COUNTIF(F12,">0")+COUNTIF(I12,">0")+COUNTIF(L12,">0")+COUNTIF(O12,">0")+COUNTIF(R12,">0")+COUNTIF(U12,">0"))


=SUM(IF(F12>0,G12,FALSE),IF(I12>0,J12,FALSE),IF(L12>0,M12,FALSE),IF(O12>0,P12,FALSE),IF(R12>0,S12,FALSE),IF(U12>0,V12,FALSE))/(COUNTIF(F12,">0")+COUNTIF(I12,">0")+COUNTIF(L12,">0")+COUNTIF(O12,">0")+COUNTIF(R12,">0")+COUNTIF(U12,">0"))

This gives me my percent survival, my average rank of those that survived, 
and the average of the number of whatevers that correspond to surviving 
units. Hope it helps. 

Thanks again for all the advice!



"cervenyc" wrote:

> I got it to work, but the formula averages a zero when the statement is 
> false. How do I make a false statement render a blank cell, or to drop the 
> value? If the value is 0, then I want it excluded, I only want to average 
> values greater than 0. Thanks.
> 
> 
> 
> "SteveG" wrote:
> 
> > 
> > Enter the formula as you normally would.
> > 
> > 
> > =AVERAGE(IF(A1:A8>0,A1:A8,FALSE))
> > 
> > When you are done typing in the formula, rather than hitting the Enter
> > key, hit the Ctrl, Shift & Enter keys simultaneously.  That will create
> > the {} around the formula automatically.  Don't type them in yourself.
> > 
> > HTH
> > 
> > Steve
> > 
> > 
> > -- 
> > SteveG
> > ------------------------------------------------------------------------
> > SteveG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7571
> > View this thread: http://www.excelforum.com/showthread.php?threadid=499913
> > 
> > 
0
cervenyc (6)
1/12/2006 5:45:02 PM
Reply:

Similar Artilces: