Solved

Adding a new condition to an existing Excel formula

Posted on 2014-10-14
6
120 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

763 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