A Formula to get First and the last value in a range

[Arnolf]

Hello,
I need to determine the first value and the last value in a range. The formula should return the name of the header.
I enclose an example. I need the formula for columns FIRST and LAST.
Please, help me.
Thank you
Arnolf.

Book1.xls
	A	B	C	D	E	F	G	H	I
2
3		January	February	March	April	May	FIRST	LAST
4		0	1	0	9	0	February	April
5		0	0	5	0	0	March	March
6		0	7		0	4	February	May
7			9	0	3		February	April
8
9		0	0	0	0	0
10		3	0	0	0	5	January	May
11
Sheet1

 

[NBVC]

Try:

In G4: =INDEX($A$1:$E$1,1,MIN(IF($A2:$E2>0,COLUMN($A2:$E2))))
In H4: =INDEX($A$1:$E$1,1,MAX(IF($A2:$E2>0,COLUMN($A2:$E2))))

Evaluated with CTRL+SHIFT+ENTER not just Enter

Then copied down.

 

[Arnolf]

Thank you NBVC,
I entered your formulas, but I cant get the results desired.
Is there somenthing I am doing wrong ?.
One more little request when there is no data or when the data is 0 in the range the formula should show blank cell.
Appreciate your help.

Book1.xls
	A	B	C	D	E	F	G	H	I	J	K
1
2							YOUR FORMULA		RESULTS DESIRED
3		January	February	March	April	May	FIRST	LAST	FIRST	LAST
4		0	1	0	9	0	March	May	February	April
5		0	0	5	0	0	April	April	March	March
6		0	7		0	4	March	#REF!	February	May
7			9	0	3		March	May	February	April
8							January	January
9		0	0	0	0	0	January	January
10		3	0	0	0	5	February	#REF!	January	May
11
Sheet1

 

[barry houdini]

Perhaps these, gives blank where there is no non-zero value in the range

In G4 copied down

=IF(SUM(B4:F4),INDEX(B$3:F$3,MATCH(TRUE,B4:F4>0,0)),"")

confirmed with CTRL+SHIFT+ENTER

in H4 copied down

=IF(SUM(B4:F4),LOOKUP(2,1/(B4:F4>0),B$3:F$3),"")

just normally entered

 

[Brian from Maui]

Try these, entered with CSE,

=INDEX(A1:M1,MATCH(TRUE,A2:M2<>0,0))

and for the last non-zero

=INDEX(A1:M1,MAX((A2:M2<>0)*COLUMN(A2:M2)))

also entered with CSE

 

[Domenic]

Try...

G4, copied down:

=INDEX($B$3:$F$3,MATCH(TRUE,INDEX(B4:F4>0,0),0))

H4, copied down:

=LOOKUP(2,1/INDEX(B4:F4>0,0),$B$3:$F$3)

Both these formulas need to be entered with just ENTER.

Hope this helps!

 

[NBVC]

Thank you NBVC,
I entered your formulas, but I cant get the results desired.
Is there somenthing I am doing wrong ?.
One more little request when there is no data or when the data is 0 in the range the formula should show blank cell.
Appreciate your help.

I figured out why they weren't working for you. The arrays need to start at column A, even though no data is entered.

If you adjust the left range to column A, both your requirements will be taken into account, (i.e. skips 0 and blanks).

Thanks.

 

[Arnolf]

Barry, Brian, Domenic, NBVC,
Great!!. Just perfect.
Gentlemen, thank you so much for your very kind solutions.
Cheers,
Arnolf.

 

[barry houdini]

=INDEX($B$3:$F$3,MATCH(TRUE,INDEX(B4:F4>0,0),0))

Domenic,

I haven't seen that one before, have to file it away for future use :wink:

 

[Domenic]

=INDEX($B$3:$F$3,MATCH(TRUE,INDEX(B4:F4>0,0),0))

Domenic,

I haven't seen that one before, have to file it away for future use :wink:

Yeah, I like it too. I got this from Aladin.