start finish dates

[Guest]

hi there


i have data laid out like this:


----------------------------------------------------------------------------------------------
Jan Feb Mar Apr May Jun Jul
A 10 11 12 13 14 15 16
B 10 11 12 13
C 10 11 12
D 10 11 12
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------


I want to write a formula that will give me the start & end months of each
row in a table like this:


-----------------------------------------------------------------------------------------------
Start End
A
B
C
D
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

so eventually it will look like this



-----------------------------------------------------------------------------------------------
Start End
A Jan Jul
B Feb May
C May Jul
D Jan Mar
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


so far, my thinking only takes me as far as using some sort of Index
formula, but i dont know how to make it work

any help gratefully recieved, let me know if youy need anymore info


cheers

jb2010

 

[Dave O]

I got your desired results by laying out the source data with A, B, C,
D in cells A2:A5, the month names as headers in B1:H1, and the integer
numbers in the grid below it, cells B2:H5. The formula to return A's
Start month is
=INDEX($B$1:$H$1,0,MATCH(MIN(B2:H2),B2:H2,0))
and the formula for A's end month is
=INDEX($B$1:$H$1,0,MATCH(MAX(B2:H2),B2:H2,0))

You can copy and paste downward to derive B, C, and D Start and End
months.

Dave O

 

[Guest]

Hi DaveO

works a treat

thank you very much

what method would i use if instead of blanks i had zeros (the zeros would
corrupt the MIN formula, wouldnt they?)

cheers again


jb