WeThotUWasAToad
asked on
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),A
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
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)
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
:)
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!
:)
ASKER
Many thanks.
ASKER
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."
:)