Solved

Adding a new condition to an existing Excel formula

Posted on 2014-10-14
6
107 Views
Last Modified: 2014-10-15
Attached is a sample spreadsheet implementing a formula (in Column J). I would like this formula revised. Column L shows the desired results I am looking for, with the new criteria that needs to be factored in highlighted in yellow (in row 4).  I've outlined the background details of the existing formula below and included the updated criteria change at the end.

CRITERIA OF EXISTING FORMULA:
I would like Column J to concatenate different columns, including a specific string within one column, based on various criteria outlined below:

If D = "CAD" or "TREP", AND H = "VOL 1" or "VOL 7" or "VOL 12" then Column J to concatenate as follows:
J = B + D + C [first word after closing bracket OR first second word in string if no closing bracket] + F
Otherwise, Column J to concatenate as follows:
J = B + D + E + F

CURRENT FORMULA:
=IF(AND(OR(H2="VOL 1",H2="VOL 7",H2="VOL 12"),OR(D2="CAD",D2="TREP")),B2&"-"&D2&"-"&LEFT(TRIM(IF(FIND(")",C2)<>LEN(C2),MID(C2,FIND(")",C2)+1,100),MID(C2,FIND(" ",C2)+1,100))),FIND(" ",TRIM(IF(FIND(")",C2)<>LEN(C2),MID(C2,FIND(")",C2)+1,100),MID(C2,FIND(" ",C2)+1,100))))-1)&IF(F2<>"","-"&F2,""),B2&"-"&D2&IF(E2&F2="","","-"&E2&F2))

CRITERIA OF UPDATED FORMULA:
As above, but including the following new conditions:
If Columns G and Column H is blank, and Column I is not blank, then  J = B + D + E + F
Otherwise:
J = B + D + C [first word after closing bracket OR first second word in string if no closing bracket] + F

Thanks,
Andrea
EE-Sample-Updated.xlsx
0
Comment
Question by:Andreamary
  • 3
  • 2
6 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40380392
There are a couple of issues:
1) You refer to column H twice - once for the "Section" (actually in column H) and the "VOL" numbers (actually in column K).  The formula in the workbook refers to column K for the volume numbers; is that correct to continue?
2) The formula in the workbook is not the same as your example (does not contain the OR clause for the volume numbers 1,7,12).
0
 

Author Comment

by:Andreamary
ID: 40380431
Sorry, Glenn!! I should have taken more time to review everything before posting!

I believe I've fixed the spreadsheet, and I've reuploaded it. Here is the current (fixed) formula in the spreadsheet:

=IF(AND(OR(K2="VOL 1",K2="VOL 7",K2="VOL 12"),OR(D2="CAD",D2="TREP")),B2&"-"&D2&"-"&LEFT(TRIM(IF(FIND(")",C2)<>LEN(C2),MID(C2,FIND(")",C2)+1,100),MID(C2,FIND(" ",C2)+1,100))),FIND(" ",TRIM(IF(FIND(")",C2)<>LEN(C2),MID(C2,FIND(")",C2)+1,100),MID(C2,FIND(" ",C2)+1,100))))-1)&IF(F2<>"","-"&F2,""),B2&"-"&D2&IF(E2&F2="","","-"&E2&F2))
EE-Sample-Fixed.xlsx
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 250 total points
ID: 40380775
This modification to your formula seems to work:
=IF(AND(NOT(AND(G2&H2="",I2<>"")),OR(K2="VOL 1",K2="VOL 7",K2="VOL 12"),OR(D2="CAD",D2="TREP")),B2&"-"&D2&"-"&LEFT(TRIM(IF(FIND(")",C2)<>LEN(C2),MID(C2,FIND(")",C2)+1,100),MID(C2,FIND(" ",C2)+1,100))),FIND(" ",TRIM(IF(FIND(")",C2)<>LEN(C2),MID(C2,FIND(")",C2)+1,100),MID(C2,FIND(" ",C2)+1,100))))-1)&IF(F2<>"","-"&F2,""),B2&"-"&D2&IF(E2&F2="","","-"&E2&F2))

I highlighted the updated portion.  Since your condition of -
Columns G and Column H is blank, and Column I is not blank, then  J = B + D + E + F
is the same as the FALSE result of the original formula, its opposite
NOT(AND(G2&H2="",I2<>""))
should follow the TRUE result.

-Glenn
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 total points
ID: 40381659
FWIW, a little shorter:

=IF(AND(OR(G2&H2<>"",I2=""),OR(K2={"VOL 1","VOL 7","VOL 12"}),OR(D2={"CAD","TREP"})),B2&"-"&D2&"-"&LEFT(TRIM(MID(C2,FIND(IF(FIND(")",C2)<>LEN(C2),")"," "),C2)+1,100)),FIND(" ",TRIM(MID(C2,FIND(IF(FIND(")",C2)<>LEN(C2),")"," "),C2)+1,100)))-1)&IF(F2<>"","-"&F2,""),B2&"-"&D2&IF(E2&F2="","","-"&E2&F2))
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40382269
Rory, I did not know you could do that (i.e., compare a value to an array of values)!  That's my cool knowledge for the week.  :-)

And darnit, I don't know why I didn't figure out the first OR clause like yours; it's much simpler and easier to follow.
0
 

Author Closing Comment

by:Andreamary
ID: 40382509
Both worked like a charm...thanks so much!
Andrea
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now