[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

Using an equation as a term 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)

In this example, the denominator (presumably a single term) consists of an equation — in this case an equality.

How can an equation (of any form) operate as a term (if that's what is happening here)?

Thanks
0
WeThotUWasAToad
Asked:
WeThotUWasAToad
  • 3
2 Solutions
 
byundtCommented:
The second term is more properly labeled a Boolean expression. When used in an Excel formula, it returns a vector or array of results. In this particular case, you get one result for each cell in the range B1:B12.

Some Excel functions expect arrays as an input. The array may comprise a range of cells or the results of a Boolean expression. LOOKUP, AGGREGATE (for first parameters 14 through 19) and SUMPRODUCT are functions that work this way.

Other Excel functions expect a single value as an input, but can tolerate an array if you array-enter the formula (Control + Shift + Enter). IF is a good example of such a function. In such cases, the function will return an array of results, one for each value in the input array. In most cases, you will pass the results of these functions into another function that is expecting an array as an input.

Still other Excel functions can handle a range of cells as an input, but need to be array-entered if passed the results of a Boolean expression. SUM is a good example of such a function.

In the particular case of Flyster's clever formula, the Boolean expression tests whether cells B1:B12 equal the value of B15. If so, the denominator returns TRUE, which is coerced into a value of 1 when used in an arithmetic expression. Should a cell in the range B1:B12 not equal B15, the denominator returns FALSE, which is coerced into a value of 0. Since 1/0 is undefined, the expression returns a DIV/0! error value at that position. Putting it all together, the expression 1/(B1:B12=B15) will return an array of 1 and DIV/0! error values.

LOOKUP has the property of considering only those values in the second parameter that are the same data type as the first parameter. In Flyster's formula, that means that the DIV/0! error values are ignored.

LOOKUP is expecting the second parameter to be sorted in ascending order. It will then find a match for the highest value less than or equal to the first parameter. In your case, the second parameter is not sorted. Furthermore, Excel will never find a match for 2 in an array of 1 and DIV/0! error values. As a result, the function returns a match for the last value of the same data type as the first parameter. Though somewhat arcane, this allows the LOOKUP formula to return a match for the last instance of B1:B12 equaling B15.
0
 
Rgonzo1971Commented:
Hi,

In excel True is equal to 1 and false is equal to 0

If you have true the 1/1 = 1
if you have false 1/0 = error

See my other answer

Regards
0
 
WeThotUWasAToadAuthor Commented:
Brad,

I’m sorry I have been so long in responding but thank you for your great explanation. It has helped a ton in understanding the steps involved in this solution. For my own future reference, I summarized them (the steps) in a couple of charts posted here:

• Range of cells equal to a single value in Excel functions

Also, I don't think I have really ever understood the term "Boolean" before. But your post forced me to look it up (thank you very much) and, like so many other things once you learn* them, it doesn't seem all that difficult.  :P

*I don't mean to imply in the slightest that I have "learned" all there is to know about it but at least it doesn't seem foreign now.

I think I've included this in a post before but one of my favorite lines is:

"I worry about all the things I know I don't know but what really terrifies me are all the things I don't know and don't even know I don't know them."

:)
0
 
WeThotUWasAToadAuthor Commented:
Rgonzo1971

Thanks also for your comment. It's one other part of the process involved here which has to be understood.

The "can't-divide-by-0" part is clear of course but the True/False = 1/0 part was not something I really even thought of. It's an Excel convention which I learned long ago but not until figuring out each step (involved in the solution equation), did I see why it's needed. In fact, I don't think I previously ever thought about why True/False might need a numerical counterpart.

Funny isn't it, how in a single solution or comment, you can feel like you have recouped a whole year's worth of your EE (subscriber) fees, and then in the very same thread, a completely different comment can make you feel the same thing all over again!

:)
0
 
WeThotUWasAToadAuthor Commented:
Many thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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