if and - if or statements

[jccamp]

The questions are shown on the attached spreadsheet. There are 2 questions one on each sheet. As this is my first post feel free to let me know if I can make my questions any clearer. On the spreadsheet the yellow cells indicate data that I'd import ( on the left) and input by hand to get the process going.

The green cells have the correct numbers in them but I've had to manipulate them by hand.

I need help with the formula logic.

thanks,
jccamp

[daddylonglegs]

For Q1 try these formulas in C5 and D5 respectively

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

=IF(AND(E5<>E4,E5="LONG"),MIN(INDEX(A$4:A4,MATCH(2,INDEX(1/(E$4:E4<>"short"),0))+1):A4),"")

copy both formulas down the columns

I note that you are taking both Max and Min from column A, is that right?

Q2

Try this formula in E5 copied down

=IF(OR(C5<>C4,D5<>D4),MIN(0.2,IF(F4<>F5,0,E4)+0.02),E4)

[jccamp]

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

[daddylonglegs]

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

[jccamp]

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

[VBA Noob]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

VBA Noob