Adding a new condition to an existing Excel formula

Posted on 2014-10-14
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.

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

=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))

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
J = B + D + C [first word after closing bracket OR first second word in string if no closing bracket] + F

Question by:Andreamary
  • 3
  • 2
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).

Author Comment

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))
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
should follow the TRUE result.

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

LVL 85

Accepted Solution

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))
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.

Author Closing Comment

ID: 40382509
Both worked like a charm...thanks so much!

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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.

773 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