| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I format a column to show a negative percentage in red? | Excel Discussion (Misc queries) | |||
| how do i sum up a column of time values but ignore negative values | New Users to Excel | |||
| how do i set up a column with negative values automatically? | Excel Discussion (Misc queries) | |||
| increase all values in a column by a percentage | Excel Worksheet Functions | |||
| Stacked column, 2 values, percentage? | Charts and Charting in Excel | |||
Linear Mode
