Working out a percentage if the number is above

[Cmorgan]

Evening,

I would appreciate any help you can give on this please:

In column JT I have a number of values ranging from: -5 to 5 and the odd blank box.

What I would like is a formula which will add up all the times a value of 3 or higher is in the column and then divide that by the number of times there is a value in the column, e.g. not including blanks.

Many thanks

[Andrew-R]

Try:

=COUNTIF(JT:JT,">=3")/COUNTA(JT:JT)

[SteveG]

Hi,

Try this.

=SUMIF(JT1:JT9,">"&3,JT1:JT9)/COUNTA(JT1:JT9)

If your blank cells have formulas in them this will not work though. You could use:

=SUMIF(JT1:JT9,">"&3,JT1:JT9)/(COUNTA(JT1:JT9)-COUNTBLANK(JT1:JT9))

HTH
Steve

[SteveG]

Sorry thought you wanted to SUM the times. Switch the sumif to the countif.

=COUNTIF(JT1:JT9,">3")/(COUNTA(JT1:JT9)-COUNTBLANK(JT1:JT9))

Steve

[Cmorgan]

Thanks for the help guys, but still a few problems:

I'm using SteveG's:

=SUMIF(Predicted!JT4:Predicted!JT140,">"&3,Predicted!JT4:Predicted!JT140)/(COUNTA(Predicted!JT4:Predicted!JT140)-COUNTBLANK(Predicted!JT4:Predicted!JT140))

but at there are formulas in the blank I tried to amend it to:

=COUNTIF(Predicted!JT4:Predicted!JT140,">"&3,Predicted!JT4:Predicted!JT140)/(COUNTA(Predicted!JT4:Predicted!JT140)-COUNTBLANK(Predicted!JT4:Predicted!JT140))

but now it comes up with a argument error. Any ideas please?

Cheers

[SteveG]

Hi,

Change the "COUNTIF(Predicted!JT4:Predicted!JT140,">"&3,Predicted!JT4:Predicted!JT140" to "COUNTIF(Predicted!JT4:JT140,">3")". You don't need the range again after the ">3".

HTH
Steve

[Cmorgan]

Many thanks, I am now using:

=COUNTIF(Predicted!JT4:JT140,">=3")/(COUNTA(Predicted!JT4:Predicted!JT140)-COUNTBLANK(Predicted!JT4:Predicted!JT140))

I now have two more steps to do:

What I would like is a formula which will add up all the times a value of 3 or higher is in the column and then divide that by the number of times there is a value in the column, e.g. not including blanks. - but it to only do this is the value of C6 is in column C.

Any ideas please.

[DGagnon]

Try this:

=COUNTIFS(Predicted!JT4:JT140,">=3",Predicted!C4:C140,C6)/(COUNTA(Predicted!JT4:Predicted!JT140)-COUNTBLANK(Predicted!JT4:Predicted!JT140))

[Cmorgan]

Thanks for this, I've figured a slight problem, I have used:

=COUNTIFS(Predicted!JT$4:JT$140,">=3",Predicted!B$4:B$140,Lookups!C2)/(COUNTA(Predicted!JT$4:Predicted!JT$140)-COUNTBLANK(Predicted!JT$4:Predicted!JT$140))

However, this is now causing problems because it is dividing the figure by the number of times there is a figure in JT, not counting the blanks which have formulas in. I actually need it to divide by the number of times there is a figure in JT, not counting the blanks which have formulas in, if the value in column B is C2.

Any ideas please.

Many thanks for all the help.