Results 1 to 14 of 14
Thread: Offset() (2000)

20020309, 07:43 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Offset() (2000)
I have a 2column 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

20020309, 09:06 #2
 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

20020309, 09:20 #3
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Offset() (2000)
Yesworks great!
Thanks,
Jeff
P.S.: In the Min calculation, what if I want to ignore zeros and cells where no data is entered?

20020309, 09:38 #4
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,509
 Thanks
 35
 Thanked 66 Times in 62 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))

20020309, 09:39 #5
 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

20020309, 09:40 #6
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,509
 Thanks
 35
 Thanked 66 Times in 62 Posts
Re: Offset() (2000)
Well, spoke too soon. That won't work if there are >1 zeros! oh well.

20020309, 09:41 #7
 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

20020309, 09:45 #8
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,509
 Thanks
 35
 Thanked 66 Times in 62 Posts
Re: Offset() (2000)
Use Aladin's earlier post. "wrt" means "with respect to"

20020309, 10:21 #9
 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

20020309, 12:10 #10
 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 arrayenter: =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 arrayenter a formula, you need to hit control+shift+enter at the same time, not just enter.
AladinMicrosoft MVP  Excel

20020309, 14:28 #11
 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 arrayformulas 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 arrayformulas.
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 ]
AladinMicrosoft MVP  Excel

20020309, 14:43 #12
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,509
 Thanks
 35
 Thanked 66 Times in 62 Posts
Re: Offset() (2000)
How's this as an alternative to finding the smallest nonzero 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)))

20020309, 14:48 #13
 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 nonzero 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.
AladinMicrosoft MVP  Excel

20020309, 14:51 #14
 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.
AladinMicrosoft MVP  Excel