Finding non contiguous positive values

G

Guest

I'm using the following to sum the last 10 non blank cells in column A (which
has over 500 rows with empty cells randomly distributed):

=Sum(A100:Index(A1:A100,Large(Row(A1:A100)*(A1:A100<>""),10)))

Now I need to find how many of those 10 cells have positive numbers so that
I can then divide by 10 and arrive at a percentage of positive cells. Any
suggestions??
 
D

Domenic

Try...

=COUNTIF(INDEX(A1:A100,LARGE(IF(A1:A100<>"",ROW(A1:A100)-ROW(A1)+1),10)):
A100,">0")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 

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

count even-numbered values in a range 5
Want to get last row that has value 2
Counting a set of contiguous similar values 3
isblank() function 6
Combine FREQUENCY and SUM of Associated Values 3
Averaging only the non-zero entries in a row? 4
Formula wanted 5
INDEX function array causes problem when attempting to use LARGEfunction to sort positive and negati 10

Top