Complicated Cell referencing Calculation code Help NEEDED

PLEASE HELP

On the attached Xls if you look on Disa 1 sheet there is some data entered. my issue is with the M:[Downtime Mins] column I have the formula in the cell. =IF(D7=9170,(G7/(J7/60)),"") It only Calculates downtime mins IF the Code "9170" is the the C:[Oper No.] col.!!!!!
But as you can see the data needed for that calculation will only be entered in the D:[Pattern No] first entry. so i need to grab that data for the first example and put the result of the downtime calculation in M11 (in this case)

To make things more complicated there will be multiple Pattern No. entered and there is no set number of Codes which is what makes it hard to reference a range and find the last cell with a D:[Pattern No] in it and reference that row.

In K11 & K12 & k13 i tired LookUp and Vlookup to see if i could get it to return the values i needed so i could do a nested calculation but i cant get it to give me the column and correct row

in the second print out sheet i gave more examples expressing how each PATTERN No. can have a different amount of codes but a downtime still needs to be calcuated for each INDIVIDUAL Pattern.


PLEASE HELP! i know this is complicated and a pain but thats why i am asking for help from the Experts.

It sounds like what you need is the bottom value in a column, which has a known solution.

M11=INDEX(C$7:C11,MATCH(REPT("z",255),C$7:C11))

Put that in M11 and pull down, and it will fill with the last entry (eg, the lowest one) in column D, down to the current row. Be aware that cells containing spaces, " ", are not blank, they just look like they are.

Note that the MATCH returns a relative depth to the INDEX--you can use that like so:

M11=INDEX(D$7:D11,MATCH(REPT("z",255),C$7:C11))

This will check along column C for the last row, but it will return the same row from Column D.

Anyway, I don't really understand how you want to use them, but that should answer your question of "how do I find the bottom value of a column and return from that row".

Thank you, i did this
=IF($C11=9170,((LOOKUP(2,1/($G5:$G11<>""),$G5:$G11))/((LOOKUP(2,1/($J5:$J11<>""),$J5:$J11))/60)),"")