lookup max min
 

Hi.

I have a table of data which looks as follows:

Name 1/5/07 8/5/07 15/5/07 22/5/07 0/0/00
a1 £50 £50 £50 £0 £0
a2 £0 £50 £50 £50 £0
a3 £0 £0 £50 £50 £0
a4 £50 £0 £0 £0 £0

I want to be able to look up the earliest and latest date that each
individual was active (i.e. £ 0), so for a1 I would want earliest date to
be 1/5/07 and latest date to be 15/5/07.

Is there an easy way to do this?

Thanks in advance

Craig

lookup max min
 

Hi Craig

The following are array entered formulae
for the latest date use
{=INDEX($A$1:$F$1,MAX((B2:F20)*COLUMN(B2:F2)))}

for the earliest date
{=INDEX($A$1:$F$1,SMALL((B2:F20)*COLUMN(B2:F2),CO UNT(B2:F2)+1-COUNTIF(B2:F2,"0")))}

To array enter a formula, (or edit the formula) use Control Shift Enter
(CSE) not just Enter.
Do not type the curly braces { } yourself. When you use CSE, Excel
will enter them for you.
--
Regards

Roger Govier


"Craig" wrote in message
...
Hi.

I have a table of data which looks as follows:

Name 1/5/07 8/5/07 15/5/07 22/5/07 0/0/00
a1 £50 £50 £50 £0 £0
a2 £0 £50 £50 £50 £0
a3 £0 £0 £50 £50 £0
a4 £50 £0 £0 £0 £0

I want to be able to look up the earliest and latest date that each
individual was active (i.e. £ 0), so for a1 I would want earliest
date to be 1/5/07 and latest date to be 15/5/07.

Is there an easy way to do this?

Thanks in advance

Craig

lookup max min
 

Asume your data in A1:F5

For minimum date
In G2: =MIN(IF($B2:$F20,$B$1:$F$1))
ctrl+shift+enter, not just enter
copy down

For maximum date
In H2: =MAX(IF($B2:$F20,$B$1:$F$1))
ctr+shift+enter, not enter
copy down


"Craig" wrote:

Hi.

I have a table of data which looks as follows:

Name 1/5/07 8/5/07 15/5/07 22/5/07 0/0/00
a1 £50 £50 £50 £0 £0
a2 £0 £50 £50 £50 £0
a3 £0 £0 £50 £50 £0
a4 £50 £0 £0 £0 £0

I want to be able to look up the earliest and latest date that each
individual was active (i.e. £ 0), so for a1 I would want earliest date to
be 1/5/07 and latest date to be 15/5/07.

Is there an easy way to do this?

Thanks in advance

Craig

lookup max min
 

Hi TM

Much nicer, and simpler.
Obviously its time I went to bed!!<g

--
Regards

Roger Govier


"Teethless mama" wrote in
message ...
Asume your data in A1:F5

For minimum date
In G2: =MIN(IF($B2:$F20,$B$1:$F$1))
ctrl+shift+enter, not just enter
copy down

For maximum date
In H2: =MAX(IF($B2:$F20,$B$1:$F$1))
ctr+shift+enter, not enter
copy down


"Craig" wrote:

Hi.

I have a table of data which looks as follows:

Name 1/5/07 8/5/07 15/5/07 22/5/07 0/0/00
a1 £50 £50 £50 £0 £0
a2 £0 £50 £50 £50 £0
a3 £0 £0 £50 £50 £0
a4 £50 £0 £0 £0 £0

I want to be able to look up the earliest and latest date that each
individual was active (i.e. £ 0), so for a1 I would want earliest
date to
be 1/5/07 and latest date to be 15/5/07.

Is there an easy way to do this?

Thanks in advance

Craig