lookup max min

[Craig]

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

 

[Roger Govier]

Hi Craig

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

for the earliest date
{=INDEX($A$1:$F$1,SMALL((B2:F2>0)*COLUMN(B2:F2),COUNT(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.

 

[Guest]

Asume your data in A1:F5

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

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

 

[Roger Govier]

Hi TM

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