Results 1 to 14 of 14

Thread: Offset() (2000)

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Offset() (2000)

    I have a 2-column worksheet of dates and data. The first column lists the dates that I record certain prices in the second column. In other words, the first column lists dates and the second column contains data.
    What I want to do is find both the maximum and minimum values in the column of data, and the date (in column 1) those max and min values occur. I know I have to use the Offset() function, but when I tried to use it, I didn't understand the directions/parameters.
    Any help?
    Thanks,
    Jeff

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset() (2000)

    You don't need OFFSET per se for what you want.

    Consider the following sample in A1:B7.

    {"Date","Price";
    37258,3.4;
    37258,2.8;
    37317,3.4;
    37470,7.8;
    37379,7.8;
    37440,6.4}

    The funny looking numbers are really dates in the internal representation of Excel.

    In D2 enter: =MIN(B:[img]/forums/images/smilies/cool.gif[/img] [ or the definite range: B2:B7 instead of B:B ]

    In D3 enter: =MAX(B:[img]/forums/images/smilies/cool.gif[/img] [ or the definite range: B2:B7 instead of B:B ]

    In E2 enter: =INDEX(A:A,MATCH(D2,B:B,0)) [ or the definite ranges: A2:A7 and B2:B7 instead of A:A and B:B, respectively ]

    Note. MATCH will return the first date value it finds associated with MAX (or MIN) value, while it is possible that there are more dates on which the price can be at its, e.g., MAX.
    Microsoft MVP - Excel

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Offset() (2000)

    Yes-works great!
    Thanks,
    Jeff
    P.S.: In the Min calculation, what if I want to ignore zeros and cells where no data is entered?

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Offset() (2000)

    Or use this, I think, for the min value that's not zero:

    =IF(MIN(B1:B9)<>0,MIN(B1:B9),SMALL(B1:B9,2))

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Offset() (2000)

    Aladin,
    Not sure I follow you: what is "wrt"?
    If you could provide a spreadsheet showing what you are telling me, that would be extremely helpful!
    Thanks,
    Jeff

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Offset() (2000)

    Well, spoke too soon. That won't work if there are >1 zeros! oh well.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Offset() (2000)

    What do I do if there are, in fact >1 zeros?
    Thanks,
    Jeff

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Offset() (2000)

    Use Aladin's earlier post. "wrt" means "with respect to"

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Offset() (2000)

    Aladin,
    I appreciate your time and patience, but I still can't quite get the hang of the Offset function. Would you mind explaining it with the attached spreadsheet, keeping in mind I want to return the minimum value of the column, excluding zeros and blanks.
    Thanks,
    Jeff
    Attached Files Attached Files

  10. #10
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset() (2000)

    Jeff,

    Last request wrt MIN & MAX calcs invites using OFFSET!

    In D1 enter: =MATCH(9.99999999999999E+307,A:A)

    In D2 array-enter: =MIN(IF(OFFSET(B2,0,0,D1,1),OFFSET(B2,0,0,D1,1)))

    In D3 enter: =MAX(OFFSET(B2,0,0,D1,1))


    To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

    Aladin
    Microsoft MVP - Excel

  11. #11
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset() (2000)

    Jeff,

    wrt is the acronym for 'with respect to'.

    It seems to me you've got the formulas of my initial reply working.

    I just changed the formulas that go in D2 and D3 into array-formulas to compute MIN and MAX such a way the zeroes and blank cells are ignored. In order to do that, I suggested an additional formula that goes into D1 whose result is used by the array-formulas.

    I'll try to attach a file: it contains more than I gave you. Just look at the formulas that I proposed. [ I deleted the attachment, fearing confusion. See the attachments posted later in the thread ]

    Aladin
    Microsoft MVP - Excel

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Offset() (2000)

    How's this as an alternative to finding the smallest non-zero value?

    =IF(MIN(B:[img]/forums/images/smilies/cool.gif[/img]<>0,MIN(B:[img]/forums/images/smilies/cool.gif[/img],SMALL(B:B,1+COUNTIF(B:B,0)))

  13. #13
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset() (2000)

    > How's this as an alternative to finding the smallest non-zero value?

    > =IF(MIN(B:[img]/forums/images/smilies/cool.gif[/img]<>0,MIN(B:[img]/forums/images/smilies/cool.gif[/img],SMALL(B:B,1+COUNTIF(B:B,0)))


    That's a good alternative to the array formula.

    Aladin
    Microsoft MVP - Excel

  14. #14
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset() (2000)

    Jeff,

    I think I forgat to include in my attachment the offset formulas that I suggested (see the edited post).

    If you want to use those formulas (see the new attachment, the modified version of yours), you can't put them underneath the data. That is, A and B must have nothing but the relevant data.

    Aladin
    Attached Files Attached Files
    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
  •