Solved

# Using an equation as a term in Excel functions

Posted on 2014-09-01
94 Views
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

LVL 80

Accepted Solution

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

LVL 47

Assisted Solution

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

Author Comment

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

Author Comment

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

Author Closing Comment

Many thanks.
0

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

#### 759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!