[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 694
  • Last Modified:

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),A1:A12)

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
0
WeThotUWasAToad
Asked:
WeThotUWasAToad
1 Solution
 
QlemoC++ DeveloperCommented:
Flyster explained it already. It does only make sense as a whole, as this formula combines several techniques:
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.
0
 
barry houdiniCommented:
Hello Steve,

I partially explained the technique in one of your other questions, but it's probably more appropriate here:

When you use this formula

=LOOKUP(2,1/(B1:B12=B15),A1:A12)

It finds the last (positionally) value in B1:B12 that matches B15 and returns the corresponding value from A1:A12.

Finding the appropriate value in one range and returning a value from another range is normal LOOKUP behaviour but the "trick" here is in how that match is made.

Note that there is no requirement for any range to be sorted for the formula to work as per my definition in bold. Of course for your scenario you want the latest date, so you need the dates sorted ascending so that the last date (positionally) is also the latest date (chronologically).

The formula works in a similar way to this formula

=LOOKUP(9.99E+307,A:A)

That finds the last number in column A. Again the range doesn't need to be sorted and could also contain error values or text - it still finds the last number in column A. Why?

For that you need to understand how LOOKUP behaves when it doesn't find the lookup value.

Because LOOKUP is supposed to work with an ascending range it can use "binary search" - only examining a limited number of values, e.g. it looks at the midpoint to see if the lookup value is > or < than that value, if it's greater it can discard the first 50% of the values and if it's < then it can discard the top 50%. The function keeps on examining the midpoint and discarding the other values - when the lookup value isn't found it's left with only one value - the last number in the range.

For this version the principle is the same - the lookup array will return only 1s or #DIV/0! errors so if you use 2 as LOOKUP value (2 is just a convention - it could be 20, 200, 2000 or 10000, any number > 1) it matches with the last 1 which corresponds with the last row where B15 matches:

Specifically if I have "x" in B15 and also have "x" in B4, B7 and B11 then B1:B12=B15 returns this array

{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}

When you divide 1 by that array you get this as the lookup range:

{#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!}

So the LOOKUP matches with the 1 at position 11 and returns the value in A11

regards, barry
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now