Results 1 to 8 of 8

20110601, 01:07 #1
 Join Date
 Feb 2009
 Posts
 9
 Thanks
 5
 Thanked 0 Times in 0 Posts
Using the OFFSET and COUNT functions
I’ve used Excel for some time, but I’ve only used the basic spreadsheet functions (e.g., add, sum, etc.).
First, let me say I’m struggle with the technicalities a bit, but would really appreciate any help explaining the effectof a formula. Based on some research, I’mu sing and OFFSET and COUNT functions. Here’s the formula I have in cell D2 =OFFSET(A1,COUNT(B:B),1). See attached worksheet.
I get the results I’m looking for, I want the last value entered in Column B. Let's say 12. Then assume on 06/01/2011 I enter a value of 18, then the results in D2 would be 18. The formula works perfect.
Here’s where my issues comes in. I needed to insert a couple of rows for a header, so my Date (Column A) and Value (Column B) were moved to row 3. This changed my results in cell D2.
Why would inserting the 2rows caused myformula to stop 2rows above my last value in Column B since my formulaincluded all the rows in Column B (B:B).
It took a little time to figure out, but it was an easy fix; I just adjusted my starting point in the OFFSET from A1 to A3.
I’m just struggling to understand why it would matter if my formula looks at all the values in Column B (B:B)? Why would inserting 2row cause this formula to stop 2rows before the last value? I was curious, so I inserted a third row and my formula result moved up another row (the value in D2 is now 10; there are three value after 10 in Column B)?
I’m sure there is an easy explanation and I would appreciate any enlightenment.
Thank you.Last edited by baseball715; 20110601 at 01:13.

20110601, 03:00 #2
 Join Date
 Dec 2009
 Location
 BC
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
The quick answer is that COUNT only counts cells with numbers  not blank cells, or cells with text  just numbers. You might have had numbers in B1 to B20, so COUNT(B:B) equals 20. Now you've inserted two rows with B1 and B2 now being blank or containing text and your last value pushed down to B22. But COUNT(B:B) still equals 20 because those first two cells don't contribute  they presumably don't contain numbers.

20110601, 07:32 #3
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
FYI, if you want the last number in column B, then:
=LOOKUP(9.99E+307,B:B)
will work regardless of text or title rows.Regards,
Rory
Microsoft MVP  Excel

The Following 2 Users Say Thank You to rory For This Useful Post:
baseball715 (20110601),nononcents (20110609)

20110601, 09:29 #4
 Join Date
 Feb 2009
 Posts
 9
 Thanks
 5
 Thanked 0 Times in 0 Posts
bjulien,
Thank you for your comments. I understand the COUNT function; however, in my formula (OFFSET AND COUNT) I'm not counting the number of cells with numbers, I'm getting the value of the last cell in Column B. I can have 50 cells with numbers, but if the last cell (let's say B51) has a value of 200, then is returned. When I insert the rows, my formula moves up to cell B49.

20110601, 09:33 #5
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
Yes you are  the COUNT part does that and the OFFSET uses that as the number of rows to go down starting from from A1. As explained, if you then insert two rows above your data and you don't have numbers in them, then you don't move down the correct number of rows from A1. Adjusting the start cell to A3 corrects this.
Regards,
Rory
Microsoft MVP  Excel

The Following User Says Thank You to rory For This Useful Post:
baseball715 (20110601)

20110602, 10:36 #6
 Join Date
 Feb 2010
 Location
 Florida
 Posts
 7
 Thanks
 2
 Thanked 0 Times in 0 Posts
Can you explain what the 9.99E+307 represents in the formula?

20110602, 11:19 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
9.99E+307 is a very large number. Excel continues to Lookup the large number and can't find any larger so it eventually will give up when it gets to the last value in the column.
Steve

The Following User Says Thank You to sdckapr For This Useful Post:
kreaves (20110602)

20110605, 01:35 #8
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Here is a b it technical explanation:
http://www.mrexcel.com/forum/showthread.php?t=310278Microsoft MVP  Excel