Offset() (2000)

Topic

New Reply

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

Reply | Quote

Replies

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: [ or the definite range: B2:B7 instead of B:B ]

In D3 enter: =MAX(B: [ 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.

Reply | Quote

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?

Reply | Quote

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))

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Use Aladin’s earlier post. “wrt” means “with respect to”

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

=IF(MIN(B:0,MIN(B:,SMALL(B:B,1+COUNTIF(B:B,0)))

Reply | Quote

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

> =IF(MIN(B:0,MIN(B:,SMALL(B:B,1+COUNTIF(B:B,0)))

That’s a good alternative to the array formula.

Aladin

Reply | Quote

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

Reply | Quote