Results 1 to 8 of 8
  1. #1
    New Lounger
    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 2-rows caused myformula to stop 2-rows 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 2-row cause this formula to stop 2-rows 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.
    Attached Files Attached Files
    Last edited by baseball715; 2011-06-01 at 02:13.

  2. #2
    New Lounger
    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.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  4. The Following 2 Users Say Thank You to rory For This Useful Post:

    baseball715 (2011-06-01),nononcents (2011-06-09)

  5. #4
    New Lounger
    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.

  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by baseball715 View Post
    in my formula (OFFSET AND COUNT) I'm not counting the number of cells with numbers
    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

  7. The Following User Says Thank You to rory For This Useful Post:

    baseball715 (2011-06-01)

  8. #6
    New Lounger
    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?

  9. #7
    WS Lounge VIP sdckapr's Avatar
    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

  10. The Following User Says Thank You to sdckapr For This Useful Post:

    kreaves (2011-06-02)

  11. #8
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kreaves View Post
    Can you explain what the 9.99E+307 represents in the formula?
    Here is a b it technical explanation:

    http://www.mrexcel.com/forum/showthread.php?t=310278
    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •