Sometimes you get too close to the tree to see the forest...

M

MJW

Ok, while this may be an easy way to induce contempt, notoriety and maybe
even a few death threats, can anyone see anything wrong with this formula?
Yes, I probably could have used ranges, I know. At any rate, I can't seem to
figure out why this doesn't work, and considering the fact that the formula's
more painfully-long than it is complex, I'm pretty sure I'm just missing
something obvious. All the character ref lengths are accurate, and I didn't
see anything off with the parentheticals, so I'm sort of befuddled as to
what's causing the formula to fail.

=if(right(p2,2)=â€yrâ€, if(or(left(p2,7)=â€ASP1 yrâ€, left(p2,7)=â€ASP 1yrâ€,
left(p2,8)=â€ASP 1 yrâ€), “A1â€, if(or(left(p2,9)=â€Basic1 yrâ€, left(p2,9)=â€Basic
1yrâ€, left(p2,10)=â€Basic 1 yrâ€), “CC1â€, if(or(left(p2,11)=â€Premium1 yrâ€,
left(p2,11)=â€Premium 1yrâ€, left(p2,12)=â€Premium 1 yrâ€), “CC+1â€,
if(or(left(p2,7)=â€ASP3 yrâ€, left(p2,7)=â€ASP3 yrâ€, left(p2,8)=â€ASP 3 yrâ€),
“A3â€, if(or(left(p2,9)=â€Basic 3yrâ€, left(p2,9)=â€Basic3 yrâ€,
left(p2,10)=â€Basic 3 yrâ€), “CC3â€, if(or(left(p2,11)=â€Premium 3yrâ€,
left(p2,11)=â€Premium3 yrâ€, left(p2,12)=â€Premium 3 yrâ€), “CC+3â€,
if(or(left(p2,7)=â€ASP5 yrâ€, left(p2,7)=â€ASP 5yrâ€, left(p2,8)=â€ASP 5 yrâ€),
“A5â€, if(or(left(p2,9)=â€Basic 5yrâ€, left(p2,9)=â€Basic5 yrâ€,
left(p2,10)=â€Basic 5 yrâ€), “CC5â€, if(or(left(p2,11)=â€Premium5 yrâ€,
left(p2,11)=â€Premium 5yrâ€, left(p2,12)=â€Premium 5 yrâ€), “CC+5â€))))))))), "")

If anyone figures this out and is in the northeast, I'd be more than happy
to buy you a drink. Or several. (I may entertain the notion of giving you
my firstborn child, too.)

Thanks,

Jamie
 
D

Dave F

What version of Excel are you using? Versions prior to XL 07 have a
limit of 7 nested functions.

Dave
 
P

Pete_UK

Before I spend a load of time scanning through it, can you explain in
words what it is trying to do and in what way it is failing - error?,
unexpected results? etc

Pete
 
D

Duke Carey

Without a doubt there's an easier way of handling this than what you've
tried. Rather than forcing us to try to decipher this monster, how about a
simple explanation of your objective and we'll try to get you started down
the right path
 
M

MJW

Hi Pete,

Looks like Dave F's response may have hit the nail on the head--I'm using
'03 SP2, and all versions prior to '07 only support 7 nested functions. Of
course, I would have expected an error similar to the "''IF' formula in
excess of 30 arguments" one gets when you exceed that value for one IF if
that was the case, but perhaps not.

But just to be prudent, the purpose of the related column is to "code" the
text in the adjacent cells that describe a certain service contract-type and
term-length. Because the service column's source is a free-form text field
with no validation, there's one correct way to do it (IE: "Premium 1 yr",
"Basic 1 yr"), but two common typo'ed values that correlate to each service
value (IE: "Premium1 yr", "Premium 1yr", "Basic1 yr", "Basic 1yr")... so to
capture all three, I've used an OR method within the nested IF's.

Thanks,

Jamie
 
M

MJW

Hi Duke,

Oh, without a doubt. I'm just not familiar with the method. Please glance
over the response I just sent to Pete in the same thread for the intent,
though I do have to add one caveat explaining the initial
"if(right(p2,2)="yr"" part--this helps to identify new service contracts
versus renewals--new contracts end in the term length abbreviation (yr),
whereas renewals end in, well, "renewal". Appreciate any help you could
provide.

6.02x10^23 thanks!

Jamie
 
M

MJW

Elkar,

Thanks! Is there a way to append values such as "A" for any ref cell with
"ASP", "CC" for any ref cell with "Basic" or "CC+" for any ref cell with
"Premium"?

Thanks Again,

Jamie
 
D

Duke Carey

Perhaps you could use

=SUBSTITUTE(P2," ","") to eliminate ALL spaces from P2,
Then, check the no spaces version for "Basic"/"Premium" using the SEARCH()
or FIND() functions. That way you needn't check separately for

Basic 1 yr
Basic 1yr
Basic1 yr
Basic1yr

Similarly, you cas use SEARCH()/FIND() to look for "yr"
 
E

Elkar

Sure, if I understand correctly, you could use:

=IF(RIGHT(A1,2)="yr",LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1))))),"")&IF(NOT(ISERROR(SEARCH("ASP",A1))),"A","")&IF(NOT(ISERROR(SEARCH("Basic",A1))),"CC","")&IF(NOT(ISERROR(SEARCH("Premium",A1))),"CC+","")

HTH,
Elkar
 
Top