Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Formula is too long, Too many arguments!

1. I agreed to these rules
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. ## 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. ## 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.

4. I agreed to these rules
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. ## 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. Super M‌oderator
Join Date
4th July 2004
Location
Posts
2,371

## 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. ## 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 06:43.

8. I agreed to these rules
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

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

#### 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