Re: Array to find relative position - is there a better way?
- From: John Michl <waxwing1@xxxxxxxxx>
- Date: Mon, 08 Oct 2007 15:47:30 -0000
I figured out an alternative that will return the position of the last
match instead of the first.
{=MATCH(1,1/((Projects=$A$1)*(MAX(IF(Projects=$A$1,Dates))=Dates)),
1)} (entered as an array formula)
Dividing the array inside this MATCH formula into 1 (i.e. 1/(rest of
formula)) creates an array of #DIV/0! values where the original array
value was 0. The remaining values will be 1 since (1/1 = 1). Since
MATCH ignores error values, the only values evaluated in the formula
are the ones that match the criteria. The Match type of 1 forces the
formula to return the position of last matching value.
Thanks for the help Bob.
- John Michl
http://excelguru.johnmichl.com coming soon.
On Oct 8, 9:55 am, John Michl <waxwi...@xxxxxxxxx> wrote:
Thanks Bob. That does help. It is much cleaner than my approach.
However, in case of a tie (i.e. two identical dates for the matching
project, it pulls the position of the first record not the last. My
complex formula does pull the last record but given that it is highly
unlikely that the a project would have two different stages on a given
day, I think I'll go with the more simple approach.
Thanks.
- John Michl
On Oct 5, 6:09 pm, "Bob Phillips" <bob....@xxxxxxxxxxxxx> wrote:
This looks simpler to me
=MATCH(1,(Projects=A1)*(MAX(IF(Projects=A1,Dates))=Dates),0)
still an arrauy formula
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"John Michl" <waxwi...@xxxxxxxxx> wrote in message
news:1191620169.836966.65230@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a large table of transactions that includes Project Number,
Transaction Date and Project Stage. The data ranges are named
Projects, Dates and Stages. The lookup value is a specific project
number that is stored in a single cell, say "A1". The data can be
sorted in random order which limits some approaches.
I've created a somewhat complex array formula that will return the
relative position number for the row that has the largest date value
for rows that match project. (If multiple rows have the same date, it
returns the last such row.) I can use this in an INDEX function to
pull the most recent stage.
Formula to return the relative position (row with in a range):
{=MAX(((Projects=A1)*Dates=MAX((Projects=A1)*Dates))*ROW(INDIRECT("A1:A"&ROWS(Projects))))}
My array formula seems overly complex but it works so I'm hoping
there's a better way. Any ideas?
- John Michl
www.johnmichl.com-Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
.
- References:
- Array to find relative position - is there a better way?
- From: John Michl
- Re: Array to find relative position - is there a better way?
- From: Bob Phillips
- Re: Array to find relative position - is there a better way?
- From: John Michl
- Array to find relative position - is there a better way?
- Prev by Date: Re: CONVERT ROMAN TO ARABIC NUMERALS
- Next by Date: Re: How can I combine two formulas Left/Mid/Right + Substitute?
- Previous by thread: Re: Array to find relative position - is there a better way?
- Next by thread: Re: How do I convert my old linked spreadsheets to MS2007?
- Index(es):
Relevant Pages
|
