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