Sum of lowest filled cells

[Jaymond Flurrie]

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

How can I count a sum of lowest five filled cells automatically, s
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 nee
a macro for that

 

[KL]

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))

 

[Bob Phillips]

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)

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"

 

[KL]

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

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)

Hi Jaymond,

Assuming you don't have empty cells, text, errors o logical values in

between, try the following formula:

 

[Bob Phillips]

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)

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 +

 

[Jaymond Flurrie]

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 hav
understood, but it points to comma before "MATCH". I have versio
11.8033.8028 (2003 SP2).

Several question:

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

 

[KL]

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/viewtopic.php?t=103168&postdays=0&postorder=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

 

[Jaymond Flurrie]

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.

 

[Bob Phillips]

=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"

 

[KL]

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

=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"

 

[Bob Phillips]

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)

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

 

[Jaymond Flurrie]

Yeah, now this works!

Thanks for everyone

 

[Bob Phillips]

Which one, Finnish version or the VBA?

--
HTH

Bob Phillips

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

"Jaymond Flurrie"