Sum of lowest filled cells

First, this is my first post, I don't know if this one has been asked already and please point that earlier topic out if necessary or move my post to where it belongs. Thank you.

How can I count a sum of lowest five filled cells automatically, so that I don't need to point those cells automatically?

Let's take an example.

I have first numbers
1
2
3
4
5
6
7
8
9
0

where I get result 0+9+8+7+6=30

Then I add one number there to get
1
2
3
4
5
6
7
8
9
0
1

so now the result should be 1+0+9+8+7=27.

What kind of formula I need to calculate these automatically? Do I need a macro for that?

Hi Jaymond,

Assuming you don't have empty cells, text, errors o logical values in between, try the following formula:

=SUM(OFFSET(INDEX(A1:A100,MATCH(9E+307,A1:A100)),,,-5))

--
Saludos,
KL


"Jaymond Flurrie" <[email protected]> wrote in message
news:[email protected]...
>
> First, this is my first post, I don't know if this one has been asked
> already and please point that earlier topic out if necessary or move my
> post to where it belongs. Thank you.
>
> How can I count a sum of lowest five filled cells automatically, so
> that I don't need to point those cells automatically?
>
> Let's take an example.
>
> I have first numbers
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 0
>
> where I get result 0+9+8+7+6=30
>
> Then I add one number there to get
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 0
> 1
>
> so now the result should be 1+0+9+8+7=27.
>
> What kind of formula I need to calculate these automatically? Do I need
> a macro for that?
>
>
> --
> Jaymond Flurrie
> ------------------------------------------------------------------------
> Jaymond Flurrie's Profile: http://www.excelforum.com/member.php...o&userid=37430
> View this thread: http://www.excelforum.com/showthread...hreadid=571044
>

Slight amendment to cater for les than 5 items

=SUM(OFFSET(INDEX(A1:A100,MATCH(99^99,A1:A100)),,,-MIN(COUNTA(A1:A100),5)))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi Jaymond,
>
> Assuming you don't have empty cells, text, errors o logical values in
between, try the following formula:
>
> =SUM(OFFSET(INDEX(A1:A100,MATCH(9E+307,A1:A100)),,,-5))
>
> --
> Saludos,
> KL
>
>
> "Jaymond Flurrie"
<[email protected]> wrote in
message
> news:[email protected]...
> >
> > First, this is my first post, I don't know if this one has been asked
> > already and please point that earlier topic out if necessary or move my
> > post to where it belongs. Thank you.
> >
> > How can I count a sum of lowest five filled cells automatically, so
> > that I don't need to point those cells automatically?
> >
> > Let's take an example.
> >
> > I have first numbers
> > 1
> > 2
> > 3
> > 4
> > 5
> > 6
> > 7
> > 8
> > 9
> > 0
> >
> > where I get result 0+9+8+7+6=30
> >
> > Then I add one number there to get
> > 1
> > 2
> > 3
> > 4
> > 5
> > 6
> > 7
> > 8
> > 9
> > 0
> > 1
> >
> > so now the result should be 1+0+9+8+7=27.
> >
> > What kind of formula I need to calculate these automatically? Do I need
> > a macro for that?
> >
> >
> > --
> > Jaymond Flurrie
> > ------------------------------------------------------------------------
> > Jaymond Flurrie's Profile:
http://www.excelforum.com/member.php...o&userid=37430
> > View this thread:
http://www.excelforum.com/showthread...hreadid=571044
> >
>

Good catch, thank you Bob. BTW I like the 99^99 although it is farther away from the max number than 9E307 (Excel adds the +
authomatically).

--
Saludos,
KL


"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
> Slight amendment to cater for les than 5 items
>
> =SUM(OFFSET(INDEX(A1:A100,MATCH(99^99,A1:A100)),,,-MIN(COUNTA(A1:A100),5)))
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi Jaymond,
>>
>> Assuming you don't have empty cells, text, errors o logical values in
> between, try the following formula:
>>
>> =SUM(OFFSET(INDEX(A1:A100,MATCH(9E+307,A1:A100)),,,-5))
>>
>> --
>> Saludos,
>> KL
>>
>>
>> "Jaymond Flurrie"
> <[email protected]> wrote in
> message
>> news:[email protected]...
>> >
>> > First, this is my first post, I don't know if this one has been asked
>> > already and please point that earlier topic out if necessary or move my
>> > post to where it belongs. Thank you.
>> >
>> > How can I count a sum of lowest five filled cells automatically, so
>> > that I don't need to point those cells automatically?
>> >
>> > Let's take an example.
>> >
>> > I have first numbers
>> > 1
>> > 2
>> > 3
>> > 4
>> > 5
>> > 6
>> > 7
>> > 8
>> > 9
>> > 0
>> >
>> > where I get result 0+9+8+7+6=30
>> >
>> > Then I add one number there to get
>> > 1
>> > 2
>> > 3
>> > 4
>> > 5
>> > 6
>> > 7
>> > 8
>> > 9
>> > 0
>> > 1
>> >
>> > so now the result should be 1+0+9+8+7=27.
>> >
>> > What kind of formula I need to calculate these automatically? Do I need
>> > a macro for that?
>> >
>> >
>> > --
>> > Jaymond Flurrie
>> > ------------------------------------------------------------------------
>> > Jaymond Flurrie's Profile:
> http://www.excelforum.com/member.php...o&userid=37430
>> > View this thread:
> http://www.excelforum.com/showthread...hreadid=571044
>> >
>>
>
>

I like it too KL. I think I first saw RagDyer use it, it is really just a
big number that is needed not the max, and it just seems more intuitive.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"KL" <[email protected]> wrote in message
news:[email protected]...
> Good catch, thank you Bob. BTW I like the 99^99 although it is farther
away from the max number than 9E307 (Excel adds the +
> authomatically).
>
> --
> Saludos,
> KL
>
>
> "Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> > Slight amendment to cater for les than 5 items
> >
> >
=SUM(OFFSET(INDEX(A1:A100,MATCH(99^99,A1:A100)),,,-MIN(COUNTA(A1:A100),5)))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "KL" <[email protected]> wrote in message
> > news:%[email protected]...
> >> Hi Jaymond,
> >>
> >> Assuming you don't have empty cells, text, errors o logical values in
> > between, try the following formula:
> >>
> >> =SUM(OFFSET(INDEX(A1:A100,MATCH(9E+307,A1:A100)),,,-5))
> >>
> >> --
> >> Saludos,
> >> KL
> >>
> >>
> >> "Jaymond Flurrie"
> > <[email protected]> wrote in
> > message
> >> news:[email protected]...
> >> >
> >> > First, this is my first post, I don't know if this one has been asked
> >> > already and please point that earlier topic out if necessary or move
my
> >> > post to where it belongs. Thank you.
> >> >
> >> > How can I count a sum of lowest five filled cells automatically, so
> >> > that I don't need to point those cells automatically?
> >> >
> >> > Let's take an example.
> >> >
> >> > I have first numbers
> >> > 1
> >> > 2
> >> > 3
> >> > 4
> >> > 5
> >> > 6
> >> > 7
> >> > 8
> >> > 9
> >> > 0
> >> >
> >> > where I get result 0+9+8+7+6=30
> >> >
> >> > Then I add one number there to get
> >> > 1
> >> > 2
> >> > 3
> >> > 4
> >> > 5
> >> > 6
> >> > 7
> >> > 8
> >> > 9
> >> > 0
> >> > 1
> >> >
> >> > so now the result should be 1+0+9+8+7=27.
> >> >
> >> > What kind of formula I need to calculate these automatically? Do I
need
> >> > a macro for that?
> >> >
> >> >
> >> > --
> >> > Jaymond Flurrie
> >>
> ------------------------------------------------------------------------
> >> > Jaymond Flurrie's Profile:
> > http://www.excelforum.com/member.php...o&userid=37430
> >> > View this thread:
> > http://www.excelforum.com/showthread...hreadid=571044
> >> >
> >>
> >
> >
>

This formula =SUM(OFFSET(INDEX(A1:A100,MATCH(99^99,A1:A100)),,, -MIN(COUNTA(A1:A100),5)))

does not work. Probably I just didn't get something that I should have understood, but it points to comma before "MATCH". I have version 11.8033.8028 (2003 SP2).

Several question:

What are those OFFSET, MATCH and the one that makes me wonder the most, three commas?

Hi Jaymond,


> This formula =SUM(OFFSET(INDEX(A1:A100,MATCH(99^99,A1:A100)),,,
> -MIN(COUNTA(A1:A100),5)))
> does not work. Probably I just didn't get something that I should have
> understood, but it points to comma before "MATCH".

You don't mention the language of your OIffice, but most probably your sistem is configured to use semicolon ";" as list separator.
Try replacing commas by semicolons.

> What are those
> OFFSET

Have a look at Excel's Help for explanation of the use of the function

> MATCH

Have a look at Excel's Help and here: http://www.mrexcel.com/board2/viewto...er=asc&start=0 (Aladin
talks about LOOKUP, but the principle is the same)

> and the one that makes me wonder the most,
> three commas?

Theese have to do with OFFSET agian and simply denote some skipped (unused) arguments within the function

--
Regards,
KL

I corrected the formula to be =SUM(OFFSET(INDEX(A1:A100;MATCH(99^99;A1:A100));;; -MIN(COUNTA(A1:A100);5)))

Now it asks if numbers are a name.

And to make this extremely easy for you guys, my Excel's language is Finnish...

I am pretty sure it is some of these OFFSET, INDEX, MATCH or COUNTA, but do we have a Excel-dictionary English-Finnish-English somewhere? I think that it has been translated in Finnish, and that's why it doesn't work. Just guessing.

=SUMMA(SIIRTYMÄ(INDEKSI(A1:A100;VASTINE(99^99;A1:A100));;;
-MIN(LASKE.A(A1:A100);5)))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jaymond Flurrie"
<[email protected]> wrote in
message news:[email protected]...
>
> I corrected the formula to be
> =SUM(OFFSET(INDEX(A1:A100;MATCH(99^99;A1:A100));;;
> -MIN(COUNTA(A1:A100);5)))
>
> Now it asks if numbers are a name.
>
> And to make this extremely easy for you guys, my Excel's language is
> Finnish...
>
> I am pretty sure it is some of these OFFSET, INDEX, MATCH or COUNTA,
> but do we have a Excel-dictionary English-Finnish-English somewhere? I
> think that it has been translated in Finnish, and that's why it doesn't
> work. Just guessing.
>
>
> --
> Jaymond Flurrie
> ------------------------------------------------------------------------
> Jaymond Flurrie's Profile:
http://www.excelforum.com/member.php...o&userid=37430
> View this thread: http://www.excelforum.com/showthread...hreadid=571044
>

And to answer your question about an Excel Dictionary - try downloading and installing the add-in called TranslateIT from the
following page:

http://members.chello.nl/jvolk/keepitcool/download.html

--
Saludos,
KL


"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
> =SUMMA(SIIRTYMÄ(INDEKSI(A1:A100;VASTINE(99^99;A1:A100));;;
> -MIN(LASKE.A(A1:A100);5)))
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Jaymond Flurrie"
> <[email protected]> wrote in
> message news:[email protected]...
>>
>> I corrected the formula to be
>> =SUM(OFFSET(INDEX(A1:A100;MATCH(99^99;A1:A100));;;
>> -MIN(COUNTA(A1:A100);5)))
>>
>> Now it asks if numbers are a name.
>>
>> And to make this extremely easy for you guys, my Excel's language is
>> Finnish...
>>
>> I am pretty sure it is some of these OFFSET, INDEX, MATCH or COUNTA,
>> but do we have a Excel-dictionary English-Finnish-English somewhere? I
>> think that it has been translated in Finnish, and that's why it doesn't
>> work. Just guessing.
>>
>>
>> --
>> Jaymond Flurrie
>> ------------------------------------------------------------------------
>> Jaymond Flurrie's Profile:
> http://www.excelforum.com/member.php...o&userid=37430
>> View this thread: http://www.excelforum.com/showthread...hreadid=571044
>>
>
>

Easiest way is to enter it in VBA, like

Activecell.Formula =
"=SUM(OFFSET(INDEX(A1:A100,MATCH(99^99;A1:A100)),,,-MIN(COUNTA(A1:A100),5)))
"

Excel translates it automatically

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"KL" <[email protected]> wrote in message
news:[email protected]...
> And to answer your question about an Excel Dictionary - try downloading
and installing the add-in called TranslateIT from the
> following page:
>
> http://members.chello.nl/jvolk/keepitcool/download.html
>
> --
> Saludos,
> KL
>
>
> "Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> > =SUMMA(SIIRTYMÄ(INDEKSI(A1:A100;VASTINE(99^99;A1:A100));;;
> > -MIN(LASKE.A(A1:A100);5)))
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Jaymond Flurrie"
> > <[email protected]> wrote in
> > message
news:[email protected]...
> >>
> >> I corrected the formula to be
> >> =SUM(OFFSET(INDEX(A1:A100;MATCH(99^99;A1:A100));;;
> >> -MIN(COUNTA(A1:A100);5)))
> >>
> >> Now it asks if numbers are a name.
> >>
> >> And to make this extremely easy for you guys, my Excel's language is
> >> Finnish...
> >>
> >> I am pretty sure it is some of these OFFSET, INDEX, MATCH or COUNTA,
> >> but do we have a Excel-dictionary English-Finnish-English somewhere? I
> >> think that it has been translated in Finnish, and that's why it doesn't
> >> work. Just guessing.
> >>
> >>
> >> --
> >> Jaymond Flurrie
>
>> ------------------------------------------------------------------------
> >> Jaymond Flurrie's Profile:
> > http://www.excelforum.com/member.php...o&userid=37430
> >> View this thread:
http://www.excelforum.com/showthread...hreadid=571044
> >>
> >
> >
>

Yeah, now this works!

Thanks for everyone!

Which one, Finnish version or the VBA?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jaymond Flurrie"
<[email protected]> wrote in
message news:[email protected]...
>
> Yeah, now this works!
>
> Thanks for everyone!
>
>
> --
> Jaymond Flurrie
> ------------------------------------------------------------------------
> Jaymond Flurrie's Profile:
http://www.excelforum.com/member.php...o&userid=37430
> View this thread: http://www.excelforum.com/showthread...hreadid=571044
>