if and - if or statements

daddylonglegs,

thats amazing!!!!! i don't think i could have deduced that with my present knowledge. i have no idea how that works ( and i stepped thru with the formula auditing tool) hopefully i can learn how all those commands -- that are certainly new to me -- work by using help.

by the way you were right about me referring to the wrong column in my example thanks for picking up on my intent. i altered your formula and that column works perfectly now --see attached.

there is one small thing: the other column -- see attached -- in the very first output has an n/a in it instead of 52.5. i have put the spreadsheets side to side and everything is perfect except for this. since i have no clue how your statement does what it does - i still need help.



thanks again,
jccamp

Actually I altered my original suggestion. If I'd left it as it was then it would have worked.....

Try this slightly altered version in C5 copied down

=IF(AND(E5<>E4,E5="SHORT"),MAX(INDEX(A$3:A4,MATCH(2,INDEX(1/(E$3:E4<>"long"),0))+1):A4),"")

The column D formula should probably be altered in a similar way

well how cool is that?


that worked!!!


i'll not even ask how until i've studied it enough to at least ask a semi-intelligent question. (i dont need any help looking silly)

thanks again
jcccamp