Error in cell formula

[Jerry Manner]

Hi

Almost whole day I am struggling with cell formula below.

=IF(OR(LEFT(C86;3)="729";LEFT(C86;3)="732");"551";IF(OR(LEFT(C86;3)="803";LEFT(C86;3)="733";LEFT(C86;3)="734");"552";IF(OR(LEFT(C86;3)="740";LEFT(C86;3)="824");"770";IF(OR(LEFT(C86;3)="736";LEFT(C86;3)="735");"553";IF(LEFT(C86;3)="804";"773";IF(LEFT(C86;3)="737";"770";IF(LEFT(C86;3)="406";"124";IF(C40(C86;3)="738";"775";""))))))))

If I click the enter button I get a MS excel error message telling me
that the formula you typed contains an error. I have double checked
the formula for hours, counted the open and closing bracket. I cannot
find the error in the formula.

I hope that someone else with a fresh look can quickly see the
problem.

Regards

 

[Mike H]

Jerry,

It's nested too deeply, use vlookup instead.
Build a table like this ones, I think I've got all your conditions correct
but check it.
Note my table is in A828:B840 bit it could be anywhere
729 552
732 552
803 552
733 552
734 552
740 770
824 770
736 553
735 553
804 773
737 770
406 124
738 775

Then use this formula
=VLOOKUP(LEFT(C863,3)+0,A828:B840,2,FALSE)


Mike

 

[Mike H]

Sorry forgot to add my version of Excel use , compared to your ; so you will
need to change it.

Mike

 

[Stefi]

Try to use comma as argument separator! Original English function names
require comma, some national language versions use semicolon, but in this
case function names are also translated.

Regards,
Stefi


„Jerry Manner†ezt írta:

 

[Joel]

first there 9is a limit of 8 nested IF's in excel. You have 9. Second, in
USA the semicolons should be commas.

I would use lookup instead

=LOOKUP(LEFT(C86,3),{"0","406","729","732","733","734","735","736";"","124","551","551","552","552","553",""})

 

[Jerry Manner]

first there 9is a limit of 8 nested IF's in excel. You have 9.  Second, in
USA the semicolons should be commas.

I would use lookup instead

=LOOKUP(LEFT(C86,3),{"0","406","729","732","733","734","735","736";"","124"­,"551","551","552","552","553",""})








- Tekst uit oorspronkelijk bericht weergeven -

Hi

Thank you fro responding.

I have chenged the if formula to the formula below.

=LOOKUP(LEFT(C86,3),
{"406","632","644","729","732","733","734","735","736","737","738","740","803","804","824";
124,337,337,551,551,552,552,553,553,770,775,770,552,773,770})

But when clicking the enter button I still get the error in formula
message. What can be wrong with the forumla?

Regards

 

[Joel]

Paste the formula in the Fx box of the worksheet, not directly into the cell.
I pasted the code from the postting into my worksheet and there wre no
errors.


If you get N/A then it means the first three characters doesn't match
anything if the lookup array.

 

[Jerry Manner]

Paste the formula in the Fx box of the worksheet, not directly into the cell.
 I pasted the code from the postting into my worksheet and there wre no
errors.

If you get N/A then it means the first three characters doesn't match
anything if the lookup array.







- Tekst uit oorspronkelijk bericht weergeven -

Hi

I have pasted the formula into the formula box at the top of the xecel
file behind the = character. Isn't that the right way to do this? Or
do I have to press the fx button first?

Regards

 

[Joel]

Your correct. You can edit any formula either in the cell or by activating
the cell with the mouse and edit the formula in the fx box. You can try this
with a simple formula like =A1 + B1 as an experiment.

I don't know which country you are in. There are some differences between
languages that may prevvent it from working. I'm in the USA and can't always
repeat problem people have in different countries.

also I'm using excel 2003. I believe this code should wrok with any version
of excel (excluding excel 97).

 

[Jerry Manner]

Your correct.  You can edit any formula either in the cell or by activating
the cell with the mouse and edit the formula in the fx box.  You can trythis
with a simple formula like =A1 + B1 as an experiment.

I don't know which country you are in.  There are some differences between
languages that may prevvent it from working.  I'm in the USA and can't always
repeat problem people have in different countries.

also I'm using excel 2003.  I believe this code should wrok with any version
of excel (excluding excel 97).






- Tekst uit oorspronkelijk bericht weergeven -

Hi

I have Microsoft Office Standard Edition 2003 (11.8211.8202)SP3. I
think that you almost have the same version. It is strange that when I
copy a very simple formula from excel help ( like =LOOKUP("C",
{"a","b","c","d";1,2,3,4})) I receive an error message.

Regards

 

[Joel]

See if you have the add-in. Probably the Analysis Tool Pack is missing

Tools - Addin
Analysis tool Pack
Analysis tool Pack - VBA
Internet Assistant

 

[Jerry Manner]

See if you have the add-in.  Probably the Analysis Tool Pack is missing

Tools - Addin
Analysis tool Pack
Analysis tool Pack - VBA
Internet Assistant






- Tekst uit oorspronkelijk bericht weergeven -

Hi

Today I checked if I had the add ins like you mentioned. I saw them in
the lest, selected them and clicked OK. But when I enter a simpel
formula like :
=LOOKUP("C",{"a","b","c","d";1,2,3,4})

I still recieve an error ( The formula you typed contains an error).
Am I missing soomething in my Excel application in order to enter
formulas?

Regards

 

[Jerry Manner]

Hi

Today I checked if I had the add ins like you mentioned. I saw them in
the lest, selected them and clicked OK. But when I enter a simpel
formula like :
=LOOKUP("C",{"a","b","c","d";1,2,3,4})

I still recieve an error ( The formula you typed contains an error).
Am I missing soomething in my Excel application in order to enter
formulas?

Regards- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi

Finally the problem is solved. I entered the complete formula
character for character into the function arguments windows. In this
way I could see exactly what was wrong with the formula and solve it.