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(C 40(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

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

"Jerry Manner" wrote:

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(C 40(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

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

Mike

"Jerry Manner" wrote:

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(C 40(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

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:

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(C 40(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

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" wrote:

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(C 40(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

On 4 apr, 13:39, Joel wrote:
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" wrote:
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";L*EFT(C86;3)="733";LEFT(C8 6;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(C8*6;3)="804";"773";IF(LE FT(C86;3)="737";"770";IF(LEFT(C86;3)="406";"124";I F(C*40(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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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,55 2,773,770})

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

Regards

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" wrote:

On 4 apr, 13:39, Joel wrote:
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" wrote:
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";LÂ*EFT(C86;3)="733";LEFT(C 86;3)="734");"552";IF(OR(LEFT(C86;3)="740";LEFT(C8 6;Â*3)="824");"770";IF(OR(LEFT(C86;3)="736";LEFT(C 86;3)="735");"553";IF(LEFT(C8Â*6;3)="804";"773";IF (LEFT(C86;3)="737";"770";IF(LEFT(C86;3)="406";"124 ";IF(CÂ*40(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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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,55 2,773,770})

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

Regards

On 4 apr, 14:42, Joel wrote:
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" wrote:
On 4 apr, 13:39, Joel wrote:
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" wrote:
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";L**EFT(C86;3)="733";LEFT(C 86;3)="734");"552";IF(OR(LEFT(C86;3)="740";LEFT(C8 6*;*3)="824");"770";IF(OR(LEFT(C86;3)="736";LEFT(C 86;3)="735");"553";IF(LEFT(*C8*6;3)="804";"773";IF (LEFT(C86;3)="737";"770";IF(LEFT(C86;3)="406";"124 ";I*F(C*40(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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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","8*03","804","824";
124,337,337,551,551,552,552,553,553,770,775,770,55 2,773,770})

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

Regards- Tekst uit oorspronkelijk bericht niet weergeven -

- 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

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" wrote:

On 4 apr, 14:42, Joel wrote:
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" wrote:
On 4 apr, 13:39, Joel wrote:
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","5 52","553",""})

"Jerry Manner" wrote:
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";LÂ*Â*EFT(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";LE FT(C86;3)="735");"553";IF(LEFT(Â*C8Â*6;3)="804";"7 73";IF(LEFT(C86;3)="737";"770";IF(LEFT(C86;3)="406 ";"124";IÂ*F(CÂ*40(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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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","8Â*03","804","824";
124,337,337,551,551,552,552,553,553,770,775,770,55 2,773,770})

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

Regards- Tekst uit oorspronkelijk bericht niet weergeven -

- 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

On 4 apr, 15:58, Joel wrote:
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" wrote:
On 4 apr, 14:42, Joel wrote:
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" wrote:
On 4 apr, 13:39, Joel wrote:
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","55 2","553",""})

"Jerry Manner" wrote:
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";L***EFT(C86;3)="733";LEFT( C86;3)="734");"552";IF(OR(LEFT(C86;3)="740";LEFT(C 8*6*;*3)="824");"770";IF(OR(LEFT(C86;3)="736";LEFT (C86;3)="735");"553";IF(LEF*T(*C8*6;3)="804";"773" ;IF(LEFT(C86;3)="737";"770";IF(LEFT(C86;3)="406";" 124*";I*F(C*40(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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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","8**03","804","824";
124,337,337,551,551,552,552,553,553,770,775,770,55 2,773,770})

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

Regards- Tekst uit oorspronkelijk bericht niet weergeven -

- 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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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

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" wrote:

On 4 apr, 15:58, Joel wrote:
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" wrote:
On 4 apr, 14:42, Joel wrote:
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" wrote:
On 4 apr, 13:39, Joel wrote:
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" wrote:
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";LÂ*Â*Â*EFT(C86;3)="733";LE FT(C86;3)="734");"552";IF(OR(LEFT(C86;3)="740";LEF T(C8Â*6Â*;Â*3)="824");"770";IF(OR(LEFT(C86;3)="736 ";LEFT(C86;3)="735");"553";IF(LEFÂ*T(Â*C8Â*6;3)="8 04";"773";IF(LEFT(C86;3)="737";"770";IF(LEFT(C86;3 )="406";"124Â*";IÂ*F(CÂ*40(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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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","8Â*Â*03","804","824";
124,337,337,551,551,552,552,553,553,770,775,770,55 2,773,770})

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

Regards- Tekst uit oorspronkelijk bericht niet weergeven -

- 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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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

On 4 apr, 17:32, Joel wrote:
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" wrote:
On 4 apr, 15:58, Joel wrote:
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" wrote:
On 4 apr, 14:42, Joel wrote:
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" wrote:
On 4 apr, 13:39, Joel wrote:
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","5 52","553",""})

"Jerry Manner" wrote:
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";L****EFT(C86;3)="733";LEFT (C86;3)="734");"552";IF(OR(LEFT(C86;3)="740";LEFT( C*8*6*;*3)="824");"770";IF(OR(LEFT(C86;3)="736";LE FT(C86;3)="735");"553";IF(L*EF*T(*C8*6;3)="804";"7 73";IF(LEFT(C86;3)="737";"770";IF(LEFT(C86;3)="406 ";"*124*";I*F(C*40(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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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","8***03","804","824";
124,337,337,551,551,552,552,553,553,770,775,770,55 2,773,770})

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

Regards- Tekst uit oorspronkelijk bericht niet weergeven -

- 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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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

On 7 apr, 09:38, Jerry Manner wrote:
On 4 apr, 17:32, Joel wrote:





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" wrote:
On 4 apr, 15:58, Joel wrote:
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" wrote:
On 4 apr, 14:42, Joel wrote:
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" wrote:
On 4 apr, 13:39, Joel wrote:
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" wrote:
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";L*****EFT(C86;3)="733";LEF T(C86;3)="734");"552";IF(OR(LEFT(C86;3)="740";LEFT (*C*8*6*;*3)="824");"770";IF(OR(LEFT(C86;3)="736"; LEFT(C86;3)="735");"553";IF*(L*EF*T(*C8*6;3)="804" ;"773";IF(LEFT(C86;3)="737";"770";IF(LEFT(C86;3)=" 406*";"*124*";I*F(C*40(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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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","8****03","804","824";
124,337,337,551,551,552,552,553,553,770,775,770,55 2,773,770})

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

Regards- Tekst uit oorspronkelijk bericht niet weergeven -

- 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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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- Tekst uit oorspronkelijk bericht niet weergeven -

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