WeThotUWasAToad
asked on
Reciprocal as an argument in Excel functions
Formula in Excel to display the date for the last occurrence of a value in a table
Hello,
This is a follow-up question to the above thread. The formula given (by Flyster) for the solution was:
=LOOKUP(2,1/(B1:B12=B15),A
My question in this thread relates to the structure of the 2nd term:
1/(B1:B12=B15)
I have seen (and I now use) a number of solutions — usually from Barry Houdini — which include a reciprocal argument. (Is that correct terminology, ie for when a function is a denominator)? However, I don't really understand the rationale or mechanism at work and therefore cannot exploit the same approach in other similar situations.
Can someone provide a brief explanation of reciprocal arguments or point me to a resource which explains this form?
Thanks
Hello,
This is a follow-up question to the above thread. The formula given (by Flyster) for the solution was:
=LOOKUP(2,1/(B1:B12=B15),A
My question in this thread relates to the structure of the 2nd term:
1/(B1:B12=B15)
I have seen (and I now use) a number of solutions — usually from Barry Houdini — which include a reciprocal argument. (Is that correct terminology, ie for when a function is a denominator)? However, I don't really understand the rationale or mechanism at work and therefore cannot exploit the same approach in other similar situations.
Can someone provide a brief explanation of reciprocal arguments or point me to a resource which explains this form?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
B5 = max(B5:B6) is 1 if B5 is the max, else 0
Dividing 1 thru that gives 1 or #Div/0, an invalid value.
Looking up value 2 in that results leads to accessing the row with the max value.