Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Formula is too long, Too many arguments!

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Formula is too long, Too many arguments!

    Hi,

    I am just starting to try and do some basic formulas in Excel and thought I was doing well until I came up against a "forumula too long" error.

    I am trying to get the cell G4 to display a sales number based on information provided from user input on a different worksheet. However, the information required is two fold; (1) the amount of clients (worksheet 2, cell f12) and (2)their consumption level (worksheet 2, cellf14).

    Now if the user selects; "0-49" (amount of clients) and "modest" (consumption level) I want cell G4 on worksheet 1 to calculate the sales figure ((worksheet3,c14)*25.

    Alternatively if the user selects; "0-49" (amount of clients) and "heavy" (consumption level) I want cell G4 on worksheet 1 to calculate the sales figure ((worksheet3,c14*3)*25

    Their are 7 variables for amount of clients and 4 variables for consumption level.

    I have been using IF((AND statements but agains get stuck as it is too long. Is there a more efficient way to do this?

    I could really use some help here as I am now pretty lost.

    Thanks so much for your help!!

    Mebs

  • #2
    Re: Formula is too long, Too many arguments!

    It sounds to me as though you should look at using VLOOKUP/MATCH/INDEX formula
    rather than nested IF's.

    Can you attach a sample workbook?
    Boo!

    Comment


    • #3
      Re: Formula is too long, Too many arguments!

      Sometimes if I can't get all the formulas I want in a single cell, I will use multiple cells, running formulas in each until I end up with the result in a single cell.
      I LOVE Spam please use my email for all spam: [email protected]

      Comment


      • #4
        Re: Formula is too long, Too many arguments!

        Hey Guys,

        Thanks for the help. I changed a lot of the names so they would be shorter and that seemed to help. I think my problem lies in that I have more than 7 arguments.

        This is the code I have so far:

        =IF((AND(Var!F12="0-49",Var!F14="Light")),L,
        IF((AND(Var!F12="50-99",Var!F14="Light")),L.1,
        IF((AND(Var!F12="100-149",Var!F14="Light")),L.2,
        IF((AND(Var!F12="150-199",Var!F14="Light")),L.3,
        IF((AND(Var!F12="200-249",Var!F14="Light")),L.4,
        IF((AND(Var!F12="250-299",Var!F14="Light")),L.5))))))

        Where L-L.5 are defined Names (mathematical formulas)

        ... and it works fine, however I want to include the same fromula for "Moderate" and "Heavy" which creates too many arguments, I think?

        Any suggestions would be greatly appreciated...

        Thanks again!!

        mebs

        Comment


        • #5
          Re: Formula is too long, Too many arguments!

          Attaching your worksheet will be way more easy for the people trying to help you. The (correct) formulas can then be written down immediately in your workbook.

          Wigi
          Regards,

          Wigi

          Excel MVP 2011-2014

          For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

          -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

          Comment


          • #6
            Re: Formula is too long, Too many arguments!

            Is this what you mean?

            =IF((Var!F14="Light")+(Var!F14="Moderate")+(Var!F14="Heavy"),IF(Var!F12="0-49",L,IF(Var!F12="50-99",L.1,IF(Var!F12="100-149",L.2,IF(Var!F12="150-199",L.3,IF(Var!F12="200-249",L.4,IF(Var!F12="250-299",L.5,"")))))),"")

            Comment


            • #7
              Re: Formula is too long, Too many arguments!

              I believe it is a maximum of 7 nests you can do with IF formulas. Like I previously said, you can use cells the same way as if you nest. You can end up with infinite "nesting' in this fashion.

              A1 =IF(1+2=3,1,0)
              A2 =IF(3+4=7,1,0)
              A3 =IF(Sum(A1+A2))=2,1,0)
              etc....

              You get the idea?
              Last edited by max_lux; January 31st, 2006, 06:43.
              I LOVE Spam please use my email for all spam: [email protected]

              Comment


              • #8
                Re: Formula is too long, Too many arguments!

                Hey Guys,

                Thanks to all your suggestions (max_lux), I got it working the way I wanted!

                Thanks again guys!!

                REALLY

                Comment

                Trending

                Collapse

                There are no results that meet this criteria.

                Working...
                X