unknown range

Jon von der Heyden

MrExcel MVP, Moderator

Joined

Apr 6, 2004

Messages

10,912

Office Version

1. 365

Platform

1. Windows

• Apr 19, 2006

• #2

Hi, welcome to the board!

Will you have any blank cells in the column?

If not I think you could use:
=SUM(OFFSET($A$2,0,0,COUNTA($A$2:$A$65536),1))

* This assumes A1 contains a label....

Good luck

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Apr 19, 2006

• #3

Hi,

Try:

=SUM(A1:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

 

Upvote 0

Jon von der Heyden

MrExcel MVP, Moderator

Joined

Apr 6, 2004

Messages

10,912

Office Version

1. 365

Platform

1. Windows

• Apr 19, 2006

• #4

Fairwinds, what's the +307 do? Seen this a few times (I think) but still haven't grasped the concept yet??

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Apr 19, 2006

• #5

It is just how you show a big number. I think it is called scientific notation in english.

9.99999999999999E+307
is actually the largest number you manually can enter in excel.

 

Upvote 0

Jon von der Heyden

MrExcel MVP, Moderator

Joined

Apr 6, 2004

Messages

10,912

Office Version

1. 365

Platform

1. Windows

• Apr 19, 2006

• #6

Cool, that's worth remembering.

Can I be a pain and ask why this formula works? From my perspective this would be the same as =SUM(A1:26) *in my example*, yet it works perfectly?

I.e. in my example the INDEX(MATCH()) returns 26 (thats how many rows of numbers I have)...?

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Apr 19, 2006

• #7

When you use a formula like LOOKUP, VLOOKUP with last condition TRUE or MATCH with last condition 1 or omitted, i.e. funktions that looks for the largest value less than or equat to lookup value in a sorted list, you will, if you use a lookup value larger than any value in the range, get the last numerical value as return.

 

Upvote 0

Jon von der Heyden

MrExcel MVP, Moderator

Joined

Apr 6, 2004

Messages

10,912

Office Version

1. 365

Platform

1. Windows

• Apr 19, 2006

• #8

Sorry Fairwinds I'm still lost, I get the concept of the lookup finding the last value by using the higest number, but I'm stuck on why it returns =SUM($A$1:$A$27)

Book3
	A	B	C	D
1	Number #
2	1		Formula	26
3	1
4	1
5	1
6	1
7	1
8	1
9	1
10	1
11	1
12	1
13	1
14	1
15	1
16	1
17	1
18	1
19	1
20	1
21	1
22	1
23	1
24	1
25	1
26	1
27	1
Sheet1

This is the outcome if I evaluate the formula:
E1: =SUM(A1:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

E2: =SUM($A$1:INDEX($A:$A,27))
(q) why do the references become fixed ($)?

E3: =SUM($A$1:$A$27)
(q) INDEX($A:$A,27) returns a value of 1, why does it create cell reference $A$27?

I would love to understand the logic behind this, far more useful than using the COUNTA() since COUNTA() is constrained by empty cells within the range.

Thanks.

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Apr 19, 2006

• #9

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

does actually not return a value when used in a formula like this. It returns a range, in this case A27. (If you put the formula in the sheet it will return the contents of that range i.e. 1).


So in this case the formula should be evaluated as:

=SUM(A1:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

=SUM(A1:A27)

 

Upvote 0

D

dyc428

New Member

Joined

Apr 18, 2006

Messages

8

• Apr 19, 2006

• #10

Thanks for the help guys,

I want to learn about writing macros. I was wondering if anyone knows a beginner book I can get that has plenty of examples and exercises.

Thanks

 

Upvote 0