Prev: how do i round a quotient to specific decimal places?
Next: Update Cell in Multiple Sheets - Macro
From: May 1950rc May on 3 Mar 2010 19:26
I have a column; some cells contain numbers and other cells are blank. I
keep adding numbers and blanks. How do I create a formula that will
automatically calculate the sum of the last 5 numbers every time I add a
number.

Thank you
From: Domenic on 3 Mar 2010 19:59
Let's assume that Column A, starting at A2, contains the data. First define
a dynamic named range...

Name: DynRange

Refers to:

=$A$2:INDEX($A:$A,MATCH(9.99999999999999E+307,$A:$A ))

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(INDEX(DynRange,LARGE(IF(DynRange<>"",ROW(DynRange)-MIN(ROW(DynRange))+1),5)):INDEX(DynRange,ROWS(DynRange)))

--
Domenic
Microsoft MVP - Excel
www.xl-central.com, "Your Quick Reference to Excel Solutions"

"May 1950rc" <May 1950rc(a)discussions.microsoft.com> wrote in message
news:78EE4E2A-72EA-413C-B34A-BBF5267A1078(a)microsoft.com...
> I have a column; some cells contain numbers and other cells are blank. I
> keep adding numbers and blanks. How do I create a formula that will
> automatically calculate the sum of the last 5 numbers every time I add a
> number.
>
> Thank you

From: Jarek Kujawa on 4 Mar 2010 03:35
another way:

=SUM(OFFSET(A1,MAX(IF(ISNUMBER($A$1:$A$30),ROW($A$1:$A$30)))-1,,-5,1))
this is an array formula so CTRL+SHIFT+ENTER this formula instead of
simply using ENTER

adjust yr range to fit


On 4 Mar, 01:26, May 1950rc <May 195...(a)discussions.microsoft.com>
wrote:
> I have a column; some cells contain numbers and other cells are blank.  I
> keep adding numbers and blanks.  How do I create a formula that will
> automatically calculate the sum of the last 5 numbers every time I add a
> number.
>
> Thank you

From: Ron Rosenfeld on 4 Mar 2010 08:19
On Thu, 4 Mar 2010 00:35:02 -0800 (PST), Jarek Kujawa <blinok(a)gazeta.pl> wrote:

>=SUM(OFFSET(A1,MAX(IF(ISNUMBER($A$1:$A$30),ROW($A$1:$A$30)))-1,,-5,1))
>this is an array formula so CTRL+SHIFT+ENTER this formula instead of
>simply using ENTER
>
>adjust yr range to fit

If the OP wants to ignore the blanks, your formula does not do that.
--ron
From: Ron Rosenfeld on 4 Mar 2010 08:37
On Wed, 3 Mar 2010 16:26:01 -0800, May 1950rc <May
1950rc(a)discussions.microsoft.com> wrote:

>I have a column; some cells contain numbers and other cells are blank. I
>keep adding numbers and blanks. How do I create a formula that will
>automatically calculate the sum of the last 5 numbers every time I add a
>number.
>
>Thank you

I am assuming you wish to ignore the blanks.

In the formula, rng represents, for example, A1:A100. In Excel 2007 or higher,
rng can represent an entire column (e.g. A:A); but in earlier versions of
Excel, it must be less than a full column (e.g. A1:A65535).

Larger rng's will take longer to calculate, so use some reasonable size, or use
a dynamic formula to define it.

This formula must be **array-entered**:

=SUM(LOOKUP(LARGE(ISNUMBER(rng)*ROW(rng),{1,2,3,4,5}),ROW(rng),rng))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron
 |  Next  |  Last
Pages: 1 2
Prev: how do i round a quotient to specific decimal places?
Next: Update Cell in Multiple Sheets - Macro