summing the values in nonblank cells
 

I need to find a way to sum the last 10 nonblank values in a column of
numbers. There may be a random dispersal of blank cells in the column, but I
need to sum the last 10 values of real numbers no matter the cell range.

summing the values in nonblank cells
 

Say your column of numbers was in A1 to A100,
Try this *array* formula:

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

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Nick Krill" wrote in message
...
I need to find a way to sum the last 10 nonblank values in a column of
numbers. There may be a random dispersal of blank cells in the column, but
I
need to sum the last 10 values of real numbers no matter the cell range.

summing the values in nonblank cells
 

That seems to work great - now I need to find how many of those 10 cells have
values over zero so that I can divide by 10 and arrive at a percentage of
positive values. Any suggestions?

"ragdyer" wrote:

Say your column of numbers was in A1 to A100,
Try this *array* formula:

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

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Nick Krill" wrote in message
...
I need to find a way to sum the last 10 nonblank values in a column of
numbers. There may be a random dispersal of blank cells in the column, but
I
need to sum the last 10 values of real numbers no matter the cell range.