Lookup

[dunmore83]

Hi,

I'm not sure if this formula is possible, but I will explain my situation.

In cells G5:G24 are numerical figures that are both positive and negative.

I need to lookup the range G5:G24 for positive figures and divide that number by the total number of cells & return that result as a percentage.
i.e. 4 positive numbers divided by a total of 20 cells as a percentage = 20%.

Thanks so much in advance!

 

[Trebor76]

If all the cells in the desired range are numeric, try this:

=COUNTIF($G$5:$G$24,">0")/COUNT($G$5:$G$24)

HTH

Robert

 

[Aladin Akyurek]

Hi,

I'm not sure if this formula is possible, but I will explain my situation.

In cells G5:G24 are numerical figures that are both positive and negative.

I need to lookup the range G5:G24 for positive figures and divide that number by the total number of cells & return that result as a percentage.
i.e. 4 positive numbers divided by a total of 20 cells as a percentage = 20%.

Thanks so much in advance!

Try...

All excel versions...

=COUNTIF(G5:G24,">0")/COUNT(G5:G24)

Excel 2007 and later...

=IFERROR(COUNTIF(G5:G24,">0")/COUNT(G5:G24),"")

 

[dunmore83]

It works. Amazing. Thank you!!!!

 

[dunmore83]

Is it possible to alter this formula so it only looks for numerical values that are "greater than or equal to positive" or "less than or equal to minus 20"...??


Thanks for your help!!!

 

[Aladin Akyurek]

Is it possible to alter this formula so it only looks for numerical values that are "greater than or equal to positive" or "less than or equal to minus 20"...??


Thanks for your help!!!

Try...

=SUM(COUNTIF(G5:G24,{"<=-20",">0"}))/COUNT(G5:G24)

If you want -20 in a cell of its own...

J5: -20

Then:

=SUMPRODUCT((G5:G24>0)+(G5:G24<=J5))/COUNT(G5:G24)