percentage negative values in column
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 |
percentage negative values in column
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 "Graham" wrote: 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 |
percentage negative values in column
To answer your question:
Can anyone spot what I've done wrong ? Remove the quotations marks around the second A2 in your fromula. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Graham" wrote in message ... 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 |
percentage negative values in column
Many Thanks!!
"Mike H" wrote: 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 "Graham" wrote: 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 |
percentage negative values in column
=COUNTIF(INDIRECT("C13:C"&A2),"<0")/COUNT(INDIRECT("C13:C"&A2))*100
"Graham" wrote: 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 |
All times are GMT +1. The time now is 11:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com