Excel cell contains one of many things

Excel formula: Cell contains one of many things

=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0

If you want to test a cell to see if it contains one of several things, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER and SUMPRODUCT functions.

Context

Let's say you have a list of text strings in the range B5:B11, and you want to test each cell against another list of things in range E5:E9. In other words, for each cell in B5:B11, you want to know: does this cell contain any of the things in E5:E9?

You could start build a big formula based on nested IF statements, but that won't be any fun at all, especially if the list of things you want to check for is large.

Solution

The solution is to to create a formula that can test for multiple values and return a list of TRUE / FALSE values. Once we have that, we can process that list (an array, actually) with SUMPRODUCT.

The formula we're using looks like this:

=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0

How this formula works

The key is this snippet:

ISNUMBER(SEARCH(things,B5)

This is based on another formula (explained in detail here) that simply checks a cell for a single substring. If the cell contains the substring, the formula returns TRUE. If not, the formula returns FALSE.

However, if we give the same formula a list of things (in this case, we are using a named range called "things", E5:E11) it will give us back a list of TRUE / FALSE values. The result is actually an array that looks like this:

{TRUE;FALSE;FALSE;FALSE;FALSE}

Notice that if we have even one TRUE in the array, we know a cell contains at least one thing in the list. So, we can force the TRUE / FALSE values to 1s and 0s with a double negative (--, also called a double unary):

--ISNUMBER(SEARCH(things,B5))

which yields an array like this:

{1;0;0;0;0}

Now we process the result with SUMPRODUCT, which will add up the entire array. We know if we get a non-zero result, we have a "hit", so we use >1 to force a final result of either TRUE or FALSE.

=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0

With a hard-coded list

There's no requirement that you use a range for your list of things. If you're only looking for a small number of things, you can use a list in array format, which is called an array constant. For example, if you're just looking for the colors red, blue, and green, you can use {"red","blue","green"} like this:

=SUMPRODUCT(--ISNUMBER(SEARCH({"red","blue","green"},B5)))>0

You are a rock with a set of beliefs

You were made from the earth, like a rock. The only difference is that you can believe. You are a rock with a set of beliefs. Your beliefs make your reality. If you like things as they are, continue to believe as you do, if you want to change certain things, then change the beliefs which make those things part of your reality.

Your Defining Moment is your life changing moment, a time of rebirth

In my book Millionaire Mind Programming, I described a moment I called your Defining Moment. It's that moment where you are actually "reborn" to the life you were meant to have. It's a moment where you figure out you have just settled for what life gave you and now you realize you are ready to change direction in life.

In the book, I describe it coming at a time when I lost everything, and that would naturally be a place where you find your Defining Moment but there are others, you can find it at times when you get truly inspired, at a birth of your child, a marriage, or just when everything is going great and you once again, figure out, it isn't great enough.

When things are going just OK, and you don't want to appear to others as being too greedy, to materialistic, to big headed or even selfish. It means you are cutting yourself off from moving above where you are today. It means you are limiting yourself as well as your family.

That is far more selfish than not trying. It means you care what others think of you instead of what's best for you and your family. In the real world, other people are great to have as friends as long as they can keep up. But you shouldn't hold yourself back just to stay friends with people who don't want you to achieve more or become your best.

Those aren't friends, those are your enemies. They exist in your extended family, your friends, your "membership groups" you belong to. They are what people feel they must have to be happy when that is just a fantasy one creates based on what others have programmed into one's mind.

Other people can't make you happy. Friends don't, family doesn't, groups don't. Either you are happy or you are not, then your family, friends, and groups are just a reflection of you at whatever point you decide to be. If someone can't keep up with you on your journey, then let them go because I assure you someone else is waiting for you to get their that easily takes on their part in your life.

There are plenty of friends, family and groups for billionaires justs as there are for minimum wage earners. There will even be those that truly love you for you and not your station in life and they will be there whether you earn zero or you earn a million, whether you live in a car or you live in a mansion.

You will actually better tell who your real friends are as you go from one station to another. Those that disappeared, talked badly about you behind your back in jealousy, those where never your family, friends, or groups, you only thought they were because you lived in a make believe fantasy.

You will either live your life for yourself (and your immediate family) or you will live your life for others. Either one can work out great. One takes no effort on your part. One allows you to watch life go by from the nose bleed seats. One keeps you friends with almost everyone. One keeps you acting and behaving like what they want. One makes you a slave to their expectations. One makes you lose out on so much that life can offer. But then you want them to be happy, more happy than even yourself, so you sacrifice your life to make their life comfortable. Even though they don't really care about you, you just think they do. If they did care, they would go on your journey with you.

If you are ready to go on that journey, realize it's your journey, not theirs, they can come, but they have no say. Realize you have no reason not to go, you have no reason not to be the greatest you, you can be. There is a reason you hear the saying, look at your closest 5 friends and that is your life. It doesn't have to stay that way. You can have a Defining Moment many times in life, you can change directions anytime, from anywhere, even right now.

Need to send Large Files by Email? But your ISP says it’s too large?

Need to send Large Files by Email? But your ISP says it's too large? Well, there is a solution with a company called http://sendthisfile.com

They have a free account anyone can get with a quick "no registration" other than name and email. That alone will allow you to access a special server they use to send large files from one email to another.

Of course like most companies, they have a paid plan, but it's to mainly speed up the upload, which to me is not needed by most people, the free account seemed plenty fast.

Cheers. John Alexander

As of June 2016, Real Estate Sales Nationwide Going ….?

While our last readings from Case-Shiller says the real estate market is going strong, I'm seeing it soften. For those investors who rely on longer term pricing stability, you may want to start buying lower as well as move out inventory quicker.

cash-shiller index

Over 700,000 computers have been hit with Ransomware this year

Make sure you are protected against ransomware as it is on the rise. It hits your computer just like any other virus or malware. Opening file you receive from emails or even downloading files can infect your computer.

Not all malware are created equal. I just tested two, one I've had for several years. It missed most of the issues. I'm still testing malware software for the best one and I'll update you below once I've completed a side-by-side test.

Check back HERE FOR THE RESULTS.

Snapshot of how the Real Estate Market is Doing… Up!

Housing315

Time To Start Dumping Real Estate in These States!

Texas and Louisiana, is it time to sell your Real Estate Holdings. The party appears to be coming to an end... unless oil prices go back up fast. And they don't look like they are going any where but down for now. The market can bear a few months of a drop like this where temporary market issues affect the price; but this is a move designed to push prices down by OPEC and the last time they did this, Houston became a Ghost Town.

Texas and Louisiana as a whole also suffered because the whole State depends on the oil industry. Fracking is the new boom and will be the first to go with prices remaining in this area. Anything under the $40 price level and it compounds the problems because that's the needed price point required to frack. You can get "officially" worried if prices break below $40. I don't see that happening in the next couple of months, but anything is possible.

A two-year real estate recessions is the common and historical time frame we are facing in these States. It could take years to get back to normal. Be very careful of making any long term purchases in these oil sensitive States.

How to Play the Real Estate Market

A long term hold (15 years or more) or an immediate flip are the safest ways of playing this market for now. The risk is just to high!  The March home sale numbers will give us an indication of just how fast and bad the oil price war will hit these States.

oil prices vs real estate

Text Ads Tell, but Photos Sell!

Think of Photos as "Headlines". Headlines are designed to grab that short attention span we all have and in an instant convince the reader to continue reading our main message.  Without dynamic photos, it's hard to grab attention these days. I've found by simply adding in more color vibrancy, you can turn an ordinarily dry looking photo into something that pulls the customer in for more information instantly.

Photoshop and Gimp have a simple Vibrance Adjustment tool that makes it easy. From there, you can keep going by composite layering in different sky backgrounds for even more effect. Here are some before and after photos I've "adjusted" to make selling land, homes, or commercial buildings better.

When flipping Fixer Uppers, be careful not to "fix" the problem issues with the home itself, but rather stick to just adjusting colors and tones etc. You still want the property flaws to be apparent. In this photo, just a blue sky background and color saturation were added and adjusted with just a little brightnessadded to make the home "POP."

 

This kitchen photo was just whitened up to rid the artificial light source's yellowing effect.

fixerKItch   fixerKItch2

Land photos can show amazing differences. The camera catches light and colors that Photoshop can quickly use to make dull scenes come to life.

landmrdFrt5landmrdFrt9

Mood can make all the difference as well in a photo, Different sky backgrounds and color hues make it easy.

vinceyoung2vinceyoung1

Whatever kind of property you are selling, Photoshop can help you by creating that Magic "Picture Headline" that will draw in you customer.

Rehab Funds for your existing home… Look into a Title 1 Loan from FHA

Once you own a home, you can use this program for fixup funds.

 Summary:

Under Title I, HUD insures lenders against most losses on home improvement loans.

house1

Purpose:

The Federal Housing Administration (FHA) makes it easier for consumers to obtain affordable home improvement loans by insuring loans made by private lenders to improve properties that meet certain requirements. "Lending institutions make loans from their own funds to eligible borrowers to finance these improvements."

Type of Assistance:
The Title I program insures loans to finance the light or moderate rehabilitation of properties, as well as the construction of nonresidential buildings on the property. This program may be used to insure such loans for up to 20 years on either single- or multifamily properties. The maximum loan amount is $25,000 for improving a single-family home or for improving or building a nonresidential structure.

For improving a multifamily structure, the maximum loan amount is $12,000 per family unit, not to exceed a total of $60,000 for the structure. These are fixed-rate loans, for which lenders charge interest at market rates. The interest rates are not subsidized by HUD, although some communities participate in local housing rehabilitation programs that provide reduced-rate property improvement loans through Title I lenders.

FHA insures private lenders against the risk of default for up to 90 percent of any single loan. The annual premium for this insurance is $1 per $100 of the amount advanced; although this fee may be charged to the borrower separately, it is sometimes covered by a higher interest charge.

Eligible Lenders:
Only lenders approved by HUD specifically for this program can make loans covered by Title I insurance. Title I loans can be disbursed directly to the borrower or, if the loan is made through a dealer, the disbursement will be made jointly to the dealer and the borrower. While most lenders and dealers/contractors use this program responsibly, HUD urges consumers to use caution in choosing and supervising home repair dealers/contractors conducting Title I repair/renovation work.

Eligible Customers:
Eligible borrowers include the owner of the property to be improved, the person leasing the property (provided that the lease will extend at least 6 months beyond the date when the loan must be repaid), or someone purchasing the property under a land installment contract.

Eligible Activities:
Title I loans may be used to finance permanent property improvements that protect or improve the basic livability or utility of the property--including manufactured homes, single-family and multifamily homes, nonresidential structures, and the preservation of historic homes. The loans can also be used for fire safety equipment.

Application:
Applications must be submitted to a Title I approved lender. Search for a HUD-approved lender online.

Technical Guidance:
This program is authorized under Title I, Section 2, of the National Housing Act (12 U.S.C. 1703). Program regulations are in 24 CFR Part 201. The program is administered by HUD's Office of Housing-Federal Housing Administration (FHA).

http://www.hud.gov/ll/code/llslcrit.cfm   check only Title 1 Lenders to find your local lenders. Expand the search area if you don’t find one close by.