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


This is a follow-up question to the above thread. The formula given (by Flyster) for the solution was:


My question in this thread relates to the structure of the 2nd term:


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)?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

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

WeThotUWasAToadAuthor Commented:

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."

WeThotUWasAToadAuthor Commented:

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!

WeThotUWasAToadAuthor Commented:
Many thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.