Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Formula is too long, Too many arguments!

  1. #1
    Join Date
    29th January 2006
    Posts
    4

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Posts
    10,539

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th January 2006
    Location
    Another Planet
    Posts
    447

    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: max_lux@hotmail.com

  4. #4
    Join Date
    29th January 2006
    Posts
    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    5,891

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,239

    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,"")))))),"")

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    12th January 2006
    Location
    Another Planet
    Posts
    447

    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 at 05:43.
    I LOVE Spam please use my email for all spam: max_lux@hotmail.com

  8. #8
    Join Date
    29th January 2006
    Posts
    4

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. COUNTIF formula with multiple Arguments
    By slick225 in forum EXCEL HELP
    Replies: 7
    Last Post: October 18th, 2012, 18:27
  2. Display Function/Formula Arguments
    By DavidC123 in forum EXCEL HELP
    Replies: 1
    Last Post: October 4th, 2007, 10:14
  3. Nested If Formula Returns Too Many Arguments
    By markc in forum EXCEL HELP
    Replies: 2
    Last Post: November 14th, 2006, 03:23
  4. multiple array arguments in match formula
    By mangier in forum EXCEL HELP
    Replies: 3
    Last Post: June 11th, 2006, 04:30
  5. Formula too long
    By ggmiller in forum EXCEL HELP
    Replies: 4
    Last Post: November 9th, 2005, 03:42

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno