Thread: Offset() (2000)

1. 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. 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.

3. 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. 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. 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. Re: Offset() (2000)

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

7. Re: Offset() (2000)

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

8. Re: Offset() (2000)

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

9. 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

10. 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

11. 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

12. 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. 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

14. 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

Posting Permissions

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