Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in cell formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in cell formula
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in cell formula
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in cell formula
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in cell formula
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in cell formula
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in cell formula
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in cell formula
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in cell formula
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula error - cell reference | Excel Discussion (Misc queries) | |||
Error putting a formula in a cell with vba | Excel Programming | |||
error in one cell and formula in other | Excel Worksheet Functions | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
How to set a formula in a cell? Application-defined error. | Excel Programming |