| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding non contiguous positive values
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:A10 0<""),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?? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding non contiguous positive values
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! In article , Nick Krill wrote: 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:A10 0<""),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?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding last column in non-contiguous range | Excel Worksheet Functions | |||
| average values in non-contiguous cells, ignoring 0 values | Excel Worksheet Functions | |||
| Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) | |||
| ... Count, <<< Positive Values minus Negative Values >>> ... | Excel Worksheet Functions | |||
| Finding negative and positive diffrence | Excel Worksheet Functions | |||
Linear Mode
