percentage negative values in column

G

Guest

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
 
G

Guest

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
 
S

Sandy Mann

To answer your question:
Can anyone spot what I've done wrong ?
Click to expand...

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
G

Guest

Many Thanks!!

Mike H said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula w/negative no.s & Percentage 2
Excell - two conditions count if factors are true when negative nu 2
HOW DO I SEPERATE POSITIVE AND NEGATIVE VALUES FROM A COLUMN 5
Need array formula to ignore hidden values 1
Calculating current grade percentage 3
Need blank cells to remain empty 7
Negative values in a formula 2
adding positive and negative numbers 3

Top