Excel Intuition

Consulting

Tutoring

Click to Donate

AND() Function Challenge 1

Challenge yourself to apply the AND() function appropriately in a spreadsheet using logical functions to compare median income and housing cost values.

Illustration of a spreadsheet using logical functions to compare median income and housing cost values.
EI_Logical_Intro_Comp_WBDownload
Transcript(click to view)
Suzanne here. Welcome to Excel Intuition’s AND() function challenge one video. If you haven’t already done so, I recommend you download your own copy of this companion workbook, because that’s how you will be able to challenge yourself and try this AND() function without first seeing the solution. We’re going to be working in the worksheet named Median Housing Affordability.

In this worksheet we have

gathered information from various sources in order to create a “What If Analysis” of what it costs to purchase owner occupied housing of the median value in the United States as a whole and in communities in Monterey County. And we’ve also gathered information about the monthly housing expense to purchase a property such as that, the median cost to rent

property in the US as a whole and in Monterey County communities, and we’ve gathered the median household income for the U.S as a whole and for Monterey counties, and this information is for the 2021 time period. And our question is, this is the challenge:

We’ve already used an IF() function to say whether or not the median income could afford to purchase a median value home in the US as a whole and in those communities, and we’ve already used an IF() function to say whether or not the median income could afford to rent

housing at a median level of value. So now, we’d like to use an AND() function, this is the challenge, insert a function in this column that returns the logical value TRUE if the monthly income available for housing is enough to freely choose to either purchase or rent median priced housing. So the income is sufficient to buy,

and that test would turn out to be true, and the income is sufficient to rent, and that test would turn out to be true. So this is your chance to insert the AND() function for yourself, in your file. You might want to pause this video while you do that. And now, I’m going to demonstrate that AND() function. So I could just test, is this equal “yes” and is this equal “yes.” But instead, I’m going to put the original tests for those outcomes in my AND() function. So I’m going to have an AND() function with two logical tests. The first one will be, is the income available for housing greater than or equal to the monthly housing expense to own?

And the second logical test will be, is the income available for housing greater than or equal to

the value necessary to rent at the median level?

So the AND() function will evaluate to true if BOTH of those tests evaluate to true.

And we see that actually, in none of those cases do both of those tests evaluate to true. And you can go back and see that the problem is that at the median household income in the U.S as a whole, and in the communities in Monterey that are chosen here, it would not be possible to purchase

a property at the median value of owner occupied housing in those same populations. That doesn’t mean it wouldn’t be possible to purchase a home. But, based on all of the assumptions we’ve used here to estimate the limits of the income to the cost of the housing, it would not be possible for the person with median income to purchase a property at the median value. Thank you very much.