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.

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.

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.