Home

problems with formula

%3CLINGO-SUB%20id%3D%22lingo-sub-695509%22%20slang%3D%22en-US%22%3Eproblems%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-695509%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20experiencing%20a%20problem%20with%20this%20formula%20I%20made%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DALS(OF(C21%3D%22r%22%3BC21%3D%22x%22%3BC21%3D%E2%80%9Dw%E2%80%9D%3BC21%3D%22ta%22%3BC21%3D%22ao%22%3BC21%3D%22oa%22)%3B0%3BALS(C21%3Drec%3BC22%3BALS(C21%3Dv%3B7%2C6%3BALS(OF(C21%3Dwf%3BC21%3Dz%3BC21%3Dkv%3BC21%3Dcd%3BC21%3Dbv)%3B8%3BALS(EN((C22-C21)%26gt%3B5%3B(C22-C21)%26lt%3B%3D11%3BC21%26gt%3B0%3BC21%26lt%3B12)%3BC22-C21-0%2C5%3BALS(EN((C22-C21)%26gt%3B%3D6%3BC21%26gt%3B12%3BC22%26gt%3B%3D19)%3BC22-C21-1%2C5%3BALS(EN((C22-C21)%26gt%3B10%3BC21%26lt%3B12%3BC22%26gt%3B20)%3BC22-C21-2%3BC22-C21)))))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20gives%20me%20a%20%23NAME%20troubleshoot%20...%3C%2FP%3E%3CP%3EIs%20there%20anyone%20who%20can%20help%20me%20to%20fix%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-695509%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-695884%22%20slang%3D%22en-US%22%3ERe%3A%20problems%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-695884%22%20slang%3D%22en-US%22%3EExcel%20does%20not%20recognize%20these%20purportedly%20defined%20names%20in%20your%20formula%3A%3CBR%20%2F%3EALS%2C%20OF%2C%20rec%2C%20v%2C%20wf%2C%20z%2C%20kv%2C%20cd%2C%20bv%2C%20EN%3CBR%20%2F%3EAlso%2C%20Excel%20does%20not%20recognize%20the%20formula%20for%20being%20constructed%20with%20incorrect%20syntax.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-695979%22%20slang%3D%22en-US%22%3ERe%3A%20problems%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-695979%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3EThe%20example%20is%20in%20the%20dutch%20language...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-697285%22%20slang%3D%22en-US%22%3ERe%3A%20problems%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-697285%22%20slang%3D%22en-US%22%3EPlease%20verify%20whether%20those%20I%20enumerated%20represent%20a%20function%20name%20or%20a%20defined%20name.%20Except%20for%20ALS%2C%20OF%2C%20and%20EN%2C%20all%20others%20are%20purported%20names%20that%20were%20not%20defined.%20Thus%2C%20Excel%20doesn%E2%80%99t%20recognize%20them%20as%20names%20and%20returns%20the%20%23NAME%3F%20error.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-698255%22%20slang%3D%22en-US%22%3ERe%3A%20problems%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-698255%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361472%22%20target%3D%22_blank%22%3E%40TexelFred%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%3E%3DALS(%0A%20%20%20OF(C21%3D%22r%22%3BC21%3D%22x%22%3BC21%3D%22w%22%3BC21%3D%22ta%22%3BC21%3D%22ao%22%3BC21%3D%22oa%22)%3B%0A%20%20%200%3B%0A%20%20%20ALS(C21%3D%22rec%22%3B%0A%20%20%20%20%20%20C22%3B%0A%20%20%20%20%20%20ALS(C21%3D%22v%22%3B%0A%20%20%20%20%20%20%20%20%207%2C6%3B%0A%20%20%20%20%20%20%20%20%20ALS(%0A%20%20%20%20%20%20%20%20%20%20%20%20OF(C21%3D%22wf%22%3BC21%3D%22z%22%3BC21%3D%22kv%22%3BC21%3D%22cd%22%3BC21%3D%22bv%22)%3B%0A%20%20%20%20%20%20%20%20%20%20%20%208%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20ALS(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20EN((C22-C21)%26gt%3B5%3B(C22-C21)%26lt%3B%3D11%3BC21%26gt%3B0%3BC21%26lt%3B12)%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20C22-C21-0%2C5%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ALS(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20EN((C22-C21)%26gt%3B%3D6%3BC21%26gt%3B12%3BC22%26gt%3B%3D19)%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20C22-C21-1%2C5%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ALS(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20EN((C22-C21)%26gt%3B10%3BC21%26lt%3B12%3BC22%26gt%3B20)%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20C22-C21-2%3BC22-C21%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20)%0A%20%20%20)%0A)%0A%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-698886%22%20slang%3D%22en-US%22%3ERe%3A%20problems%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-698886%22%20slang%3D%22en-US%22%3E%3CP%3EI%20found%20the%20solution%2C%20there%20was%20a%20problem%20with%20%22%20%22%20around%20the%20w%20-%20it%20was%20different.%3C%2FP%3E%3CP%3EThanks%20for%20the%20info!%3C%2FP%3E%3C%2FLINGO-BODY%3E
sylviewal
New Contributor

Hi

 

I'm experiencing a problem with this formula I made:

 

=ALS(OF(C21="r";C21="x";C21=”w”;C21="ta";C21="ao";C21="oa");0;ALS(C21=rec;C22;ALS(C21=v;7,6;ALS(OF(C21=wf;C21=z;C21=kv;C21=cd;C21=bv);8;ALS(EN((C22-C21)>5;(C22-C21)<=11;C21>0;C21<12);C22-C21-0,5;ALS(EN((C22-C21)>=6;C21>12;C22>=19);C22-C21-1,5;ALS(EN((C22-C21)>10;C21<12;C22>20);C22-C21-2;C22-C21)))))))

 

It gives me a #NAME troubleshoot ...

Is there anyone who can help me to fix this?

 

Thanks!

5 Replies
Excel does not recognize these purportedly defined names in your formula:
ALS, OF, rec, v, wf, z, kv, cd, bv, EN
Also, Excel does not recognize the formula for being constructed with incorrect syntax.

@TwifooThe example is in the dutch language...

Please verify whether those I enumerated represent a function name or a defined name. Except for ALS, OF, and EN, all others are purported names that were not defined. Thus, Excel doesn’t recognize them as names and returns the #NAME? error.

@TexelFred 

Perhaps

=ALS(
   OF(C21="r";C21="x";C21="w";C21="ta";C21="ao";C21="oa");
   0;
   ALS(C21="rec";
      C22;
      ALS(C21="v";
         7,6;
         ALS(
            OF(C21="wf";C21="z";C21="kv";C21="cd";C21="bv");
            8;
            ALS(
               EN((C22-C21)>5;(C22-C21)<=11;C21>0;C21<12);
               C22-C21-0,5;
               ALS(
                  EN((C22-C21)>=6;C21>12;C22>=19);
                  C22-C21-1,5;
                  ALS(
                     EN((C22-C21)>10;C21<12;C22>20);
                     C22-C21-2;C22-C21
                  )
               )
            )
         )
      )
   )
)

I found the solution, there was a problem with " " around the w - it was different.

Thanks for the info!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
18 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies