Problem with formula, source material can't be changed

[cndctr]

Hi,

I'm maybe one step above newbie. I've figured a few things out, but if I don't go back within a certain amount of time, I forget how I got there.

I have a formula that's problematic and I don't know how to fix it. The source on which it's based is imported and can't be changed. The issue is that I'm trying to return values based on what the source says, but two of the options include a common word, so it's returning values for both.

Here's the formula:

=(IF(ISNUMBER(SEARCH("*Finished*",sheet1!BK3,1)),".85","0"))+(IF(ISNUMBER(SEARCH("*Partially Finished*",sheet1!BK3,1)),".5","0"))+(IF(ISNUMBER(SEARCH("*Unfinished*",sheet1!BK3,1)),"0","0"))+(IF(ISNUMBER(SEARCH("*Crawl*",sheet1!BK3,1)),".05","0"))+(IF(ISNUMBER(SEARCH("*Cellar*",sheet1!BK3,1)),"0","0"))+(IF(ISNUMBER(SEARCH("*Sub-Basement*",sheet1!BK3,1)),".15","0"))+(IF(ISNUMBER(SEARCH("*Exterior Access*",sheet1!BK3,1)),".15","0"))+(IF(ISNUMBER(SEARCH("*Bathroom Rough-In*",sheet1!BK3,1)),".25","0"))+(IF(ISNUMBER(SEARCH("*Other*",sheet1!BK3,1)),")","0"))

My main issue is in the first two elements; I want to return one value for "Finished" and a different value for "Partially Finished," but because the word "finished" appears in both, it's returning both values. How do I write it so it will return value a for "Finished" and value b for "Partially Finished" without returning a value for both?

The information is imported from an outside source and may include several of the conditions listed in the same cell. It might say, for example, "Partially Finished, Exterior Acess, Bathroom Rough-In." But instead of a value of (.5+.15+.25) it's returning a value of (.85+.5+.15+.25) because it's finding "Finished" inside of "Partially Finished," and counting it as a separate event.

Thank you, in advance, for your help. It is very much appreciated.

[Tony Valko]

When you have a word that is part of a longer word (or phrase) always look for the longer word (or phrase) first.

=(IF(ISNUMBER(SEARCH("*Finished*",sheet1!BK3,1)),".85","0"))+(IF(ISNUMBER(SEARCH("*Partially Finished*",sheet1!BK3,1)),".5","0"))+(IF(ISNUMBER(SEARCH("*Unfinished*",

You should look for Partially Finished first, then Unfinished, then Finished.

Are the * used as wildcards? If so they're not needed in the SEARCH function.

It's possible that you might have to search for [space]word/phrase[space].

[cndctr]

Thank you, Tony. I rearranged the formula so that "Partially Finished" came first, but it still returned the same result.

I don't recall why I used the *'s; I probably saw them in the formula I found after which I modeled this one.

You mentioned:

It's possible that you might have to search for [space]word/phrase[space].

How do I do that? I think that may be the direction I need to follow. What would the language for that formula look like?

Thank you, in advance.

[Tony Valko]

Can you post some examples of what is in BK3?

That would help us to arrive at a solution.

[cndctr]

Here's a sample:

Screen Shot 2016-07-10 at 2.08.32 PM.png

Anywhere the source cell reads "Partially Finished," it's adding twice; it adds the value for finding "Finished" and the other value for "Partially Finished," rather than one or the other. As a reminder the value for "Finished" is .85, the value for "Partially Finished" is .5 and the value for "Unfinished" is 0. When the BK cell is "Unfinished," it's returning a value of .85, I'm assuming because it found "finished" within "Unfinished." And when it says "Partially Finished," it's returning a value of 1.35, which is the .85 for "Finished" and .5 for "Partially finished."

[dmg2016]

Hi cndctr,

Sumproduct may simplify solve your problem

CTRL-Shift Enter Required:
=SUMPRODUCT(IFERROR(SEARCH(C4:C12,sheet1!BK3),0)*(D4:D12))
=SUMPRODUCT(IFERROR(SEARCH({"Finished";"Partially Finished";"Unfinished";"Crawl";"Cellar";"Sub-Basement";"Exterior Access";"Bathroom Rough-In";"Other"},sheet1!BK3),0)*({0.85;0.5;0;0.5;0;0.15;0.15;0.25;0}))

where:
search terms: C4:C12--> {"Finished";"Partially Finished";"Unfinished";"Crawl";"Cellar";"Sub-Basement";"Exterior Access";"Bathroom Rough-In";"Other"}
Weights: D4:D12--> {0.85;0.5;0;0.5;0;0.15;0.15;0.25;0}

[Tony Valko]

I can't see *.png images on this site!

[cndctr]

JPEG for Tony:

BK column.jpg

So, DMG, if I understand you correctly, I use the formulas you mentioned, then use additional columns to enter in the required values and weights, yes? I think I get it. But I'm not certain when I use ctrl-shift-enter. Also, I'm assuming I'd need to add this to each of all 40 sheets in the workbook?

[cndctr]

Forgive me, but I am out of my league here. If I attempt to use the sumproduct, I must be doing something wrong. Do I need to put the data in quotes in the reference cells? On my sheet1, I listed the values from A46 to A54 and the weights in B46:B54. Can I type in the cell just Finished, or does it have to be "Finished" in quotes? I get 0. That's it. I must be doing something wrong.

[cndctr]

I have the formula in the cell (on sheet3) as:

=ArrayFormula(SUMPRODUCT(IFERROR(SEARCH(A46:A54,sheet1!BK3),"")*(B46:B54)))

and the referring cells on a separate sheet (sheet1) as:

Attachment 469932

and I only get a result of 0.

What am I doing wrong?

[cndctr]

Alright, I figured out one issue, but I don't know that sumproduct is the right answer. At most, it should return a value of 2.4, adding together the weights of the values. Instead, I'm getting a result of 9.85, so I'm not even sure how it's coming up with that.

[xlnitwit]

Hi,

Are you using Excel or Google sheets? ArrayFormula is not an Excel function. Also, what is the reason for searching for values only to return 0 whether they are found or not?

For an Excel formula, I believe this would suffice:
=(LOOKUP(1E+100,SEARCH({"","Finished","Partially Finished"},Sheet1!BK3),{0,0.85,0.5}))+(IF(ISNUMBER(SEARCH("Crawl",Sheet1!BK3,1)),0.05,0))++(IF(ISNUMBER(SEARCH("Sub-Basement",Sheet1!BK3,1)),0.15,0))+(IF(ISNUMBER(SEARCH("Exterior Access",Sheet1!BK3,1)),0.15,0))+(IF(ISNUMBER(SEARCH("Bathroom Rough-In",Sheet1!BK3,1)),0.25,0))

[cndctr]

..........

[cndctr]

Google Sheets.

This is but one field in a sheet that includes dozens.

I'm a real estate broker and have developed my own pricing/valuation method and report because I felt that which was made available to me by my real estate firm and local real estate board was woefully lacking and nearly entirely useless. You could enter all sorts of information, but at the end of the day, it made no computation, no suggested price, etc. I'm at the mercy of the information provided in the multiple listing service, but it's consistent enough from one listing to the next that it's reliable enough.

I import the data for dozens of listings at a time into the master sheet. All the other sheets then auto-populate, saving me hours every time I start a valuation. So the basement details, i.e., finished, unfinished, partially finished, are but one field of dozens. Because I want to keep each comparable property to a single page, it all has to fit in tight quarters. The reason I'd want to return a value of zero for something is that it's going to potentially listed in the source field, but doesn't necessarily, at this time, add any value to the property. In some cases, it detracts a bit, so I may actually turn it to a negative in the future. I guess it's there as much as a placeholder as anything for the moment.

[Tony Valko]

I don't have any experience with Google spreadsheets.

[cndctr]

If it works in excel, it works in Sheets.

[dmg2016]

Cndctr,

I have never used google sheets before, but for the basement example I have attached a working model in Excel based on the specifications in the picture you posted. In addition, my answer is based on the assumption that the first letter in each word is capitalized. If that is not the case, then I would probably use a len/mid function or an if function (I try to avoid mega-formulas whenever possible).

Please Login or Register  to view this content.

*no longer an array formula so no longer control shift enter

Disregard the following comments if the your not dealing with hundreds to tens of thousands of records.

How many sheets do you need this type of formula on? Also, roughly how many fields per sheet and items per field? In addition to the number of rows per sheet. The higher number of cell references there are within each formula result in a multiplying kind of effect in the calculation process of the sheet. The sumproduct formula and most likely any formula necessary to meet your needs would likely cause an excel sheet performance to slow way down. I think the best option would be to write a programming code (python regex), which could perform this type calculate a lot quicker than a spreadsheet formula and enter it directly into a spreadsheet.