percentage negative values in column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Graham - 12 Sep 2007 13:40 GMT

Hi, I want to calculate the % of negative values in a column, using a dynamic
range. i.e No. of negative values, divided by total number of values, X100,
to give a %. The following formula works, but is returning the wrong value:
=SUM(COUNTIF(INDIRECT("$C$13:C"&A2),"<0")/(COUNT(INDIRECT("$C$13:C"&"A2")))*100)
Can anyone spot what I've done wrong ? and/or a simpler way of doing the
same thing ?
Many Thanks

Reply to this Message

Mike H - 12 Sep 2007 14:18 GMT

Graham,

OK provided no text creeps into your range:-

=(COUNT(IF(C13:INDIRECT("C"&B1)<0,C13:INDIRECT("C"&B1),FALSE))/COUNTIF(C13:INDIRECT("B"&B1),"<>"&""))*100

Mike

> Hi, I want to calculate the % of negative values in a column, using a dynamic
> range. i.e No. of negative values, divided by total number of values, X100,
[quoted text clipped - 3 lines]
> same thing ?
> Many Thanks

Reply to this Message

Graham - 12 Sep 2007 14:52 GMT

Many Thanks!!

> Graham,
>
[quoted text clipped - 11 lines]
> > same thing ?
> > Many Thanks

Reply to this Message

Sandy Mann - 12 Sep 2007 14:50 GMT

To answer your question:

> Can anyone spot what I've done wrong ?

Remove the quotations marks around the second A2 in your fromula.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Hi, I want to calculate the % of negative values in a column, using a
> dynamic
[quoted text clipped - 6 lines]
> same thing ?
> Many Thanks

Reply to this Message

Teethless mama - 12 Sep 2007 15:00 GMT

=COUNTIF(INDIRECT("C13:C"&A2),"<0")/COUNT(INDIRECT("C13:C"&A2))*100

Reply to this Message