View Full Version : Solved: Help With a Formula
The following formula is resulting in an error:
= IF(AND(LEN($B24)>=3,MID($B24,LEN($B24)-2,3)="etv"),CONCATENATE($B24," -v"),
IF(NOT(ISERROR(FIND(" n ",$A24))),CONCATENATE($B24," -n"),
if(NOT(ISERROR(FIND(" -n ",$A24))),CONCATENATE($B24," -n"),
IF(NOT(ISERROR(FIND(" adj ",$A24))),CONCATENATE($B24," Adj"),
if(NOT(ISERROR(FIND(" -adj ",$A24))),CONCATENATE($B24," Adj"),
IF(NOT(ISERROR(FIND(" modif ",$A24))),CONCATENATE($B24," Modif"),
if(NOT(ISERROR(FIND(" -modif ",$A24))),CONCATENATE($B24," Modif"),$B24))))))))
Does anything jump out as being wrong with this formula?
austenr
04-08-2010, 01:14 PM
xld will probably be along in a bit. hes great at these.
xld will probably be along in a bit. hes great at these.
Thanks. These things become blurry on the screen after you stare at them long enough.
Bob Phillips
04-08-2010, 02:39 PM
That won't work in Excel 2003, too many nested IFs.
In 2007 there are too mnay closing brackets, it should be
=IF(AND(LEN($B24)>=3,MID($B24,LEN($B24)-2,3)="etv"),CONCATENATE($B24," -v"),
IF(NOT(ISERROR(FIND(" n ",$A24))),CONCATENATE($B24," -n"),
IF(NOT(ISERROR(FIND(" -n ",$A24))),CONCATENATE($B24," -n"),
IF(NOT(ISERROR(FIND(" adj ",$A24))),CONCATENATE($B24," Adj"),
IF(NOT(ISERROR(FIND(" -adj ",$A24))),CONCATENATE($B24," Adj"),
IF(NOT(ISERROR(FIND(" modif ",$A24))),CONCATENATE($B24," Modif"),
IF(NOT(ISERROR(FIND(" -modif ",$A24))),CONCATENATE($B24," Modif"),$B24)))))))
What are you getting?
What exactly should it be doing, maybe there is a simpler way?
Bob Phillips
04-08-2010, 02:50 PM
Here is a cleaner version which also works in Excel 2003
=$B24&IF(AND(LEN($B24)>=3,MID($B24,LEN($B24)-2,3)="etv")," -v",
IF(OR(NOT(ISERROR(FIND(" n ",$A24))),NOT(ISERROR(FIND(" -n ",$A24))))," -n",
IF(OR(NOT(ISERROR(FIND(" adj ",$A24))),NOT(ISERROR(FIND(" -adj ",$A24))))," Adj",
IF(OR(NOT(ISERROR(FIND(" modif ",$A24))),NOT(ISERROR(FIND(" -modif ",$A24))))," Modif",""))))
p45cal
04-08-2010, 03:04 PM
Well, xld beat me to it.. twice. I was about to post about the extra bracket, then I noticed his, so I thought I'd add value by shortening the formula and making it compatible with xl2003, so just about to post again.. same story.
So the only thing left for me to add is that probably:
MID($B24,LEN($B24)-2,3) could be replaced with:
RIGHT($B24,3) :steamed:
That won't work in Excel 2003, too many nested IFs.
In 2007 there are too mnay closing brackets, it should be
=IF(AND(LEN($B24)>=3,MID($B24,LEN($B24)-2,3)="etv"),CONCATENATE($B24," -v"),
IF(NOT(ISERROR(FIND(" n ",$A24))),CONCATENATE($B24," -n"),
IF(NOT(ISERROR(FIND(" -n ",$A24))),CONCATENATE($B24," -n"),
IF(NOT(ISERROR(FIND(" adj ",$A24))),CONCATENATE($B24," Adj"),
IF(NOT(ISERROR(FIND(" -adj ",$A24))),CONCATENATE($B24," Adj"),
IF(NOT(ISERROR(FIND(" modif ",$A24))),CONCATENATE($B24," Modif"),
IF(NOT(ISERROR(FIND(" -modif ",$A24))),CONCATENATE($B24," Modif"),$B24)))))))
What are you getting?
What exactly should it be doing, maybe there is a simpler way?
I was thinking one could have up to seven IF statements? At any rate, in response to your question, I am trying to test against a long string in Column A, as follows:
1. To make sure the string in COL B is longer than 3 characters.
2. To append all the terms in COL B ending with "etv" with " v"
3. To test for six possible substrings in COL A, i.e., " n ", " -n ", " adj ", " -adj ", " modif " or " -modif " (having a space before and after each substring so as not to confuse it with any characters that might be a part of another word within the string) and then to concatenate the words in COL B with the appropriate suffix depending on the substring found in COL A, i.e., append as follows, either " -n", " -adj" or " -Modif" accordingly.
4. If none of the substrings are found, then I simply want to populate the cell with the the term that exists in COL B.
I've attached a small sample sheet with an example of each possible search results, including the last row which doesn't contain any of the search criteria, in which case I simply want the formula to populate the cell in COL D with the term in COL B.
I hope this helps.
Thanks,
Opv
That won't work in Excel 2003, too many nested IFs.
In 2007 there are too mnay closing brackets, it should be
=IF(AND(LEN($B24)>=3,MID($B24,LEN($B24)-2,3)="etv"),CONCATENATE($B24," -v"),
IF(NOT(ISERROR(FIND(" n ",$A24))),CONCATENATE($B24," -n"),
IF(NOT(ISERROR(FIND(" -n ",$A24))),CONCATENATE($B24," -n"),
IF(NOT(ISERROR(FIND(" adj ",$A24))),CONCATENATE($B24," Adj"),
IF(NOT(ISERROR(FIND(" -adj ",$A24))),CONCATENATE($B24," Adj"),
IF(NOT(ISERROR(FIND(" modif ",$A24))),CONCATENATE($B24," Modif"),
IF(NOT(ISERROR(FIND(" -modif ",$A24))),CONCATENATE($B24," Modif"),$B24)))))))
What are you getting?
What exactly should it be doing, maybe there is a simpler way?
It just states that there is an error in the formula and highlights the next to the last FIND.
Here is a cleaner version which also works in Excel 2003
=$B24&IF(AND(LEN($B24)>=3,MID($B24,LEN($B24)-2,3)="etv")," -v",
IF(OR(NOT(ISERROR(FIND(" n ",$A24))),NOT(ISERROR(FIND(" -n ",$A24))))," -n",
IF(OR(NOT(ISERROR(FIND(" adj ",$A24))),NOT(ISERROR(FIND(" -adj ",$A24))))," Adj",
IF(OR(NOT(ISERROR(FIND(" modif ",$A24))),NOT(ISERROR(FIND(" -modif ",$A24))))," Modif",""))))
Thanks. I modified your formula as follows:
=$B24&IF(AND(LEN($B3)>=3,MID($B3,LEN($B3)-2,3)="etv"),CONCATENATE($B3," -v"),IF(OR(NOT(ISERROR(FIND(" n ",$A3))),NOT(ISERROR(FIND(" -n ",$A3)))),CONCATENATE($B3," -n"),IF(OR(NOT(ISERROR(FIND(" adj ",$A3))),NOT(ISERROR(FIND(" -adj ",$A3)))),CONCATENATE($B3," Adj"),IF(OR(NOT(ISERROR(FIND(" modif ",$A3))),NOT(ISERROR(FIND(" -modif ",$A3)))),CONCATENATE($B3," Modif"),B3))))
This seems to be doing what I want it to do. I'll test it a bit more to make sure.
Thanks
Bob Phillips
04-08-2010, 03:37 PM
Are the expected results in column C. That just suggests that you are taking B away from A.
Did you try my last suggestion, if so, how does it figure?
Bob Phillips
04-08-2010, 03:39 PM
Thanks. I modified your formula as follows:
=$B24&IF(AND(LEN($B3)>=3,MID($B3,LEN($B3)-2,3)="etv"),CONCATENATE($B3," -v"),IF(OR(NOT(ISERROR(FIND(" n ",$A3))),NOT(ISERROR(FIND(" -n ",$A3)))),CONCATENATE($B3," -n"),IF(OR(NOT(ISERROR(FIND(" adj ",$A3))),NOT(ISERROR(FIND(" -adj ",$A3)))),CONCATENATE($B3," Adj"),IF(OR(NOT(ISERROR(FIND(" modif ",$A3))),NOT(ISERROR(FIND(" -modif ",$A3)))),CONCATENATE($B3," Modif"),B3))))
This seems to be doing what I want it to do. I'll test it a bit more to make sure.
Thanks
Shouldn't that be
=$B3&IF(AND(LEN($B24)>=3,MID($B24,LEN($B24)-2,3)="etv")," -v",
IF(OR(NOT(ISERROR(FIND(" n ",$A24))),NOT(ISERROR(FIND(" -n ",$A24))))," -n",
IF(OR(NOT(ISERROR(FIND(" adj ",$A24))),NOT(ISERROR(FIND(" -adj ",$A24))))," Adj",
IF(OR(NOT(ISERROR(FIND(" modif ",$A24))),NOT(ISERROR(FIND(" -modif ",$A24))))," Modif",""))))
or
=$B3&IF(AND(LEN($B24)>=3,RIGHT($B24,3)="etv")," -v",
IF(OR(NOT(ISERROR(FIND(" n ",$A24))),NOT(ISERROR(FIND(" -n ",$A24))))," -n",
IF(OR(NOT(ISERROR(FIND(" adj ",$A24))),NOT(ISERROR(FIND(" -adj ",$A24))))," Adj",
IF(OR(NOT(ISERROR(FIND(" modif ",$A24))),NOT(ISERROR(FIND(" -modif ",$A24))))," Modif",""))))
with p45cal's addition
Bob Phillips
04-08-2010, 03:40 PM
I was thinking one could have up to seven IF statements?
Not seven IFs, seven nested function calls. So it can be IF or CONCATENATE and so on, they all count.
Well, xld beat me to it.. twice. I was about to post about the extra bracket, then I noticed his, so I thought I'd add value by shortening the formula and making it compatible with xl2003, so just about to post again.. same story.
So the only thing left for me to add is that probably:
MID($B24,LEN($B24)-2,3) could be replaced with:
RIGHT($B24,3) :steamed:
Thanks for the tip. I'll see about incorporating it into my existing formula. Thanks again.
Opv
Not seven IFs, seven nested function calls. So it can be IF or CONCATENATE and so on, they all count.
Ah, I see. Thanks.
Opv
Shouldn't that be
=$B3&IF(AND(LEN($B24)>=3,MID($B24,LEN($B24)-2,3)="etv")," -v",
IF(OR(NOT(ISERROR(FIND(" n ",$A24))),NOT(ISERROR(FIND(" -n ",$A24))))," -n",
IF(OR(NOT(ISERROR(FIND(" adj ",$A24))),NOT(ISERROR(FIND(" -adj ",$A24))))," Adj",
IF(OR(NOT(ISERROR(FIND(" modif ",$A24))),NOT(ISERROR(FIND(" -modif ",$A24))))," Modif",""))))
or
=$B3&IF(AND(LEN($B24)>=3,RIGHT($B24,3)="etv")," -v",
IF(OR(NOT(ISERROR(FIND(" n ",$A24))),NOT(ISERROR(FIND(" -n ",$A24))))," -n",
IF(OR(NOT(ISERROR(FIND(" adj ",$A24))),NOT(ISERROR(FIND(" -adj ",$A24))))," Adj",
IF(OR(NOT(ISERROR(FIND(" modif ",$A24))),NOT(ISERROR(FIND(" -modif ",$A24))))," Modif",""))))
with p45cal's addition
You are right. I wasn't familiar with the $B3& statement so the concatenation was superfluous. I've removed it and have also incorporated p45cal's suggestion. Works fine.
Thanks,
Opv
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.