Prev: how do i round a quotient to specific decimal places?
Next: Update Cell in Multiple Sheets - Macro
From: May 1950rc on 5 Mar 2010 19:02
Ron - Thank you so much. It worked great. Much appreciated.

Ron C.

"Ron Rosenfeld" wrote:

> 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
> .
>
From: Jarek Kujawa on 8 Mar 2010 04:46
missed that one
thks


On 4 Mar, 14:19, Ron Rosenfeld <ronrosenf...(a)nospam.org> wrote:
> On Thu, 4 Mar 2010 00:35:02 -0800 (PST), Jarek Kujawa <bli...(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

First  |  Prev  | 
Pages: 1 2
Prev: how do i round a quotient to specific decimal places?
Next: Update Cell in Multiple Sheets - Macro