Nesting Multiple IF/AND Formulas

April 23, 2013 at 20:06:05
Specs: Windows
Can you nest multiple IF formulas with multiple IF/AND formulas? I have successfully nested several IF formulas and ONE IF/AND formula but I need to add one more IF/AND formula but it is telling me that I have too many arguments.. HELP! What am I doing wrong?

See More: Nesting Multiple IF/AND Formulas

Report •

#1
April 23, 2013 at 20:31:44
Just imagine how much help we could be if you posted the formula that you are having trouble with.

It's kind of hard for us to tell what you are doing wrong if we don't know what you are doing.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 23, 2013 at 20:44:26
The actual formula is -

=IF([@TYPE]='Merch. Solutions'!$C$6,[@['# STORES]]*[@[LOAD-IN]]*6, IF([@TYPE]='Merch. Solutions'!$C$8, [@['# STORES]]*[@[LOAD-IN]]*24, IF([@TYPE]='Merch. Solutions'!$C$10, [@['# STORES]]*[@[LOAD-IN]]*6, IF(AND([@TYPE]='Merch. Solutions'!$C$7,[@PRODUCT]="Winter Apple & Cinnamon SOE"),[@['# STORES]]*[@[LOAD-IN]]*12, [@['# STORES]]*[@[LOAD-IN]]*6))))

I still need to add IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man", [@['#STORES]]*[@[LOAD-IN]]*24,[@['#STORES]]*[@[LOAD-IN]]*36

But once I add this last part it tells me I have too many arguments..


Report •

#3
April 24, 2013 at 06:26:17
What application are you using this formula in? It doesn't work in Excel due to invalid characters.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 24, 2013 at 06:41:57
It is in Excel 2013.. The formula works correctly without that last IF/AND part added..

Report •

#5
April 24, 2013 at 07:21:18
I have a copy of 2013 but I haven't installed it yet. I am running 2010 and your formula returns the following error:

The name that you entered is not valid

Reason for this can include:

- The name does not being with a letter or an underscore
- The name contains a space or other invalid characters
- The name conflicts with an Excel built-in name or the name of another object in the workbook

When I click OK it highlights [@TYPE]. If I change that to a valid name, such as TYPE, I get the same error but now it highlights [@['#. So it appears that 2010 does not like that syntax.

I'll make you a deal: I'll work on trying to figure out why you can't add another IF(AND...) by rewriting you formula so that my Excel will accept it if you'll explain what these syntaxes mean:

[@TYPE]
[@['# STORES]]

Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 24, 2013 at 07:35:25
I wonder if that an Excel 2013 thing then? The naming is what excel inputs when I select the cell I'm referencing in the formula.. I believe its because the cells are within a table so the column name over rides the individual cell reference (i.e. G14, etc..)

So for example, when I try to build a simple formula such as =A1*B1, those cells are within columns named # Stores and Load-in, so when I click them to build the formula excel is imputing [@[#store]] and [@Load-in]..


Report •

#7
April 24, 2013 at 07:42:23
Yes, OK, that's it. I created a table (something I don't normally do) and it did indeed use that syntax when I referenced a cell within the table.

Where are you trying to add the additional IF(AND...) clause? Can you post the non-working formula?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
April 24, 2013 at 07:50:44
The non working formula is:

=IF([@TYPE]='Merch. Solutions'!$C$6, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$6,IF([@TYPE]='Merch. Solutions'!$C$8, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$8, IF([@TYPE]='Merch. Solutions'!$C$9,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9,IF([@TYPE]='Merch. Solutions'!$C$10, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$10, IF(AND([@TYPE]='Merch. Solutions'!$C$7, [@PRODUCT]="Winter Apple & Cinnamon"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7, IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9)))))

It tells me that I have too many arguments.. But if you take out that last IF(AND(... it works.. Grrrr!


Report •

#9
April 24, 2013 at 07:51:20
Well, after all that work, I just looked at the IF(AND...) that you are trying to add.

It appears that you have not closed the parentheses for the AND Function:

I added a close-parenthesis after the "Gingerbread Man"


IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man"), [@['#STORES]]*[@[LOAD-IN]]*24,[@['#STORES]]*[@[LOAD-IN]]*36

Let me know if that helps.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
April 24, 2013 at 07:59:54
Still no luck.. The last message I sent, the non working formula had the closed parenthesis after "Gingerbread Man"), ... and it still tells me too many arguments..

Report •

#11
April 24, 2013 at 08:56:37
OK, I think I see the problem, but I'm going to let you test it because I don't have all of your tables nor do I want to try and figure out all of your criteria.

Let's break down the last part of the formula, starting with the first IF(AND...) by the arguments:

IF(AND([@TYPE]='Merch. Solutions'!$C$7, [@PRODUCT]="Winter Apple & Cinnamon"), (logical_test)

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7, (value_if_true)

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7, (value_if_false)

IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man"), (logical_test)

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9, (value_if_true)

[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9))))) (value_if_false)

If I'm not mistaken, the second IF(AND...) needs to be the (value_if_false)
for the first IF(AND...)). In other words, you can't close out the first IF(AND...) with a (value_if_false) and then just tack another complete IF function on the end.

Does that make sense?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
April 24, 2013 at 09:02:30
Ok I think I know what you mean, just not sure how to write it... So... If:

IF(AND([@TYPE]='Merch. Solutions'!$C$7, [@PRODUCT]="Winter Apple & Cinnamon"), (logical_test)
[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7, (value_if_true)

IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man"), (logical_test)
[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9, (value_if_true)[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7, (value_if_false),
[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9))))) (value_if_false)

??? Do I put the FALSE portions at the tail end of the formula?


Report •

#13
April 24, 2013 at 10:02:36
I don't know your criteria, but typically the last value_if_false is the result you want if none of the other IF clauses are TRUE.

=IF(A1=1, 1, IF(A1=2, 2, IF(A1=3, 3, "A1 Does Not Equal 1, 2 or 3")))

In other words, the 2nd and 3rd IF's are the value_if_false arguments for the preceeding IF's and the final value_if_false ("A1 Does Not Equal 1, 2 or 3") means that none of the 3 IF's were TRUE.

In your case, your original, final value_if_false was [@['# STORES]]*[@[LOAD-IN]]*6. If that's still the final value_if_false, then your new IF(AND...) with it's value_if_true and value_if_false should go before that.

Only you know what you want returned for each condition, so only you can determine which argument goes where.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
April 24, 2013 at 10:04:35
Not sure what your doing, but see if this helps:

=IF([@TYPE]='Merch. Solutions'!$C$6,
[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$6,

IF([@TYPE]='Merch. Solutions'!$C$8,
[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$8,

IF([@TYPE]='Merch. Solutions'!$C$9,
[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9,

IF([@TYPE]='Merch. Solutions'!$C$10,
[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$10,

IF(AND([@TYPE]='Merch. Solutions'!$C$7,[@PRODUCT]="Winter Apple & Cinnamon"),
<< Your AND statement ends with the closed parentheses after Cinnamon. >>
[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7,
<< Should be an IF statement here >>
[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7,

IF(AND([@TYPE]='Merch. Solutions'!$C$9,[@PRODUCT]="Gingerbread Man"),
[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9,
[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9)))))

MIKE

http://www.skeptic.com/


Report •

#15
April 24, 2013 at 10:40:39
P.S. When this issue is finally resolved and your IF statement is working, will you be sending us some "Winter Apple & Cinnamon" and "Gingerbread Man" snacks?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#16
April 24, 2013 at 12:30:47
Still no luck! :( Is there another way, possibly VLOOKUP (which I also know very little about) that may work better?

Report •

#17
April 24, 2013 at 13:22:25
What does your new formula look like?

Try adding one additional parentheses symbol at the end and see if that helps.
In your old formula, in addition to needing another IF statement,
needed a additional paren.

MIKE

http://www.skeptic.com/


Report •

#18
April 24, 2013 at 13:32:05
We mentioned this a number of times, but I'll mention again - one last time.

Please post your most current formula.

We don't know what changes you've, so it's impossible for us to tell you what the problem is. If you don't show us what's not working, how can we possibly offer any suggestions? Each time you've posted your latest attempt, we've pointed out problems.

We need something to work with. "Still no luck!" is not something we can test.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#19
April 24, 2013 at 13:36:23
=IF([@TYPE]='Merch. Solutions'!$C$6, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$6,IF([@TYPE]='Merch. Solutions'!$C$8, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$8, IF([@TYPE]='Merch. Solutions'!$C$10, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$10, IF(AND([@TYPE]='Merch. Solutions'!$C$9,[@PRODUCT]="Gingerbread Man"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9, IF(AND([@TYPE]='Merch. Solutions'!$C$7, [@PRODUCT]="Winter Apple & Cinnamon"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7)))))

Still tells me too many arguments but I'm not confident that this new way I wrote it is correct...


Report •

#20
April 24, 2013 at 13:41:21
I have no current updated formula because nothing I've tried has worked.. The formula that is functioning right now is:

=IF([@TYPE]='Merch. Solutions'!$C$6, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$6,IF([@TYPE]='Merch. Solutions'!$C$8, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$8, IF([@TYPE]='Merch. Solutions'!$C$9,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9,IF([@TYPE]='Merch. Solutions'!$C$10, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$10, IF(AND([@TYPE]='Merch. Solutions'!$C$7, [@PRODUCT]="Winter Apple & Cinnamon"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7)))))

But I still need to figure out how to add:

IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9)


Report •

#21
April 24, 2013 at 15:04:34
Your two final IF statements are incompatible, so try this:

=IF([@TYPE]='Merch. Solutions'!$C$6,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$6,IF([@TYPE]='Merch. Solutions'!$C$8,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$8,IF([@TYPE]='Merch. Solutions'!$C$9,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9,IF([@TYPE]='Merch. Solutions'!$C$10,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$10,IF([@TYPE]='Merch. Solutions'!$C$7,IF(AND([@TYPE]='Merch. Solutions'!$C$7, [@PRODUCT]="Winter Apple & Cinnamon"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7),IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9))))))

I've added an additional IF to test whether your working with
'Merch. Solutions'!$C$7
or
'Merch. Solutions'!$C$9

Not completely sure it will work.

MIKE

http://www.skeptic.com/


Report •

#22
April 24, 2013 at 17:14:53
Thank you for the formula, it does not return any error messages but it for some reason does not recognize the IF/AND formulas.. It just ignores them..


Report •

#23
April 24, 2013 at 18:05:14
I decided to break your original formula down into it's individual clauses to see if that would help. I think I noticed something.

Keep in mind that a Nested IF statement stops when it encounters the first IF clause that evaluates to TRUE.

Let's take a look at your 5 original IF clauses and then the clause that you want to add.

1 - IF([@TYPE]='Merch. Solutions'!$C$6, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$6,

2 - IF([@TYPE]='Merch. Solutions'!$C$8, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$8,

3 -IF([@TYPE]='Merch. Solutions'!$C$9,[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9,

4 -IF([@TYPE]='Merch. Solutions'!$C$10, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$10,

5 - IF(AND([@TYPE]='Merch. Solutions'!$C$7, [@PRODUCT]="Winter Apple & Cinnamon"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$7, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$7)))))

New: IF(AND([@TYPE]='Merch. Solutions'!$C$9, [@PRODUCT]="Gingerbread Man"),[@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$F$9, [@['# STORES]]*[@[LOAD-IN]]*'Merch. Solutions'!$D$9)

Take a look at #3. If [@TYPE]='Merch. Solutions'!$C$9 is TRUE, then you are never going to get down to the IF(AND...) that you want to add. The formula will return the value_if_true for #3 and then stop.

So, even if you can figure out how to add that final IF(AND...) it will never get evaluated if it is added to the end.

The question now is:

Which do you want to take priority - the IF(AND..) that includes [@TYPE]='Merch. Solutions'!$C$9 and [@PRODUCT]="Gingerbread Man") or [@TYPE]='Merch. Solutions'!$C$9 by itself.

Once we have that answer, we can proceed.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question