Nested IF statements

Microsoft Office excel 2003
June 9, 2010 at 13:53:19
Specs: Windows XP
I would like to nest 2 IF statements into excel but I am unsure of the syntax. Here are the 2 IF statements

=IF($CA$4="Y","312.9","1")*((AN23*CALN!AJ18+MERC!AN24*CALN!AK18)/CALN!AI18)/312.9

=IF(CA4="y",ROUND(BP23,2),BP23)

Basically the first part generates numbers with decimal places , I have a cell that when I input a "y" it changes the values. I would like for it to also round it for me but I am unsure of how to nest the 2 IF statements


See More: Nested IF statements

Report •

#1
June 9, 2010 at 19:09:06
Assuming the BP23 in your second IF is where the first IF resides, how about this:

=IF($CA$4="Y",ROUND(312.9*((AN23*CALN!AJ18+MERC!AN24*CALN!AK18)/CALN!AI18)/312.9,2),((AN23*CALN!AJ18+MERC!AN24*CALN!AK18)/CALN!AI18)/312.9)


Report •

#2
June 10, 2010 at 05:29:33
=IF($CA$4="Y",ROUND(312.9* In mine I have
=IF($CA$4="Y","312.9","1")*
I get either a 312 or a 1 but in your equation I am unsure where the 1 value wil/ be expressed

Report •

#3
June 10, 2010 at 06:05:54
For the purpose of this discussion (just to make it easier to follow) let's set the 2nd part of your IF statement equal to X.

i.e.

X = ((AN23*CALN!AJ18+MERC!AN24*CALN!AK18)/CALN!AI18)/312.9

Therefore, in condensed form you have this:

=IF($CA$4="Y","312.9","1") * X

So what I think you're doing (correct me if I am wrong) is determining what to multiply X by based on whether CA4 contains "Y" or not.

Since Excel likes to treat "text numbers" (designated with the quotes) as actual numbers, you actually have this:

=IF($CA$4="Y", 312.9, 1) * X

In other words, if CA4 contains Y, multiply X by 312.9, if not, multiply X by 1. As we all know, 1 * X = X.

Therefore, your formula can be written as:

=IF($CA$4="Y",312.9 * X, X)

Therefore if you want to round one of those answers, you can use:

=IF($CA$4="Y",ROUND(312.9 * X, 2), X)

If you want to round both answers, you can use:

=IF($CA$4="Y",ROUND(312.9 * X, 2), ROUND(X, 2))


Report •

Related Solutions

#4
June 10, 2010 at 06:45:28
So based on my original formula

=IF(CA4="y",ROUND(BP23,2),BP23) =IF($CA$4="Y","312.9","1")*((AN23*CALN!AJ18+MERC!AN24*CALN!AK18)/CALN!AI18)/312.9

Can you please write the exact syntax for me I am doing something wrong


Report •

#5
June 10, 2010 at 06:57:21
I'm confused. How does BP23 enter into this?

As I said in Response 1, I am assuming that BP23 contains the whole =IF(CA4 ="Y"...) statement.

If that's correct, then I believe I gave you the proper syntax in Response 1 and explained it in Response 3.

If BP23 is unrelated to the long IF statement, you'll need to explain how BP23 and the long IF statement interact.


Report •

#6
June 10, 2010 at 07:08:46
BP23 is the cell that represents the answer from the
F($CA$4="Y","312.9","1")*((AN23*CALN!AJ18+MERC!AN24*CALN!AK18)/CALN!AI18)/312.9

if statement. Basically I have a cell that has either a 'y' in it or not. And that long if statement ending result changes depending on the "y". I would like to add the rounding function into the True value of that if statement which is cell bp23


Report •

#7
June 10, 2010 at 07:29:50
Re: I would like to add the rounding function into the True value of that if statement

That's exactly what I offered in Response 1 and explained Response 3.

Have you tried my suggestion?


Report •

#8
June 10, 2010 at 08:41:20
It does not work right , how about nesting both IF statements somehow ?

IF($CA$4="Y","312.9","1")*((AN23*CALN!AJ18+MERC!AN24*CALN!AK18)/CALN!AI18)/312.9

=IF(CA4="y",ROUND(BP23,2),BP23)

I basically want the cell bp23 to round two decimal spaces to the right if set to "y" or do nothing but display the value as is if "y" is removed


Report •

#9
June 10, 2010 at 09:09:11
It's obvious that you and I are not on the same page.

I'm moving on. If someone else wants to step in and help out, feel free.


Report •

#10
June 10, 2010 at 11:43:32
If you replace the X in DerbyDad03's formula:

=IF($CA$4="Y",ROUND(312.9 * X, 2), X)

you get:

=IF($CA$4="Y",ROUND(312.9*((AN23*CALN!AJ18+MERC!AN24*CALN!AK18)/CALN!AI18)/312.9,2),((AN23*CALN!AJ18+MERC!AN24*CALN!AK18)/CALN!AI18)/312.9)

Does that work?

MIKE

http://www.skeptic.com/


Report •

Ask Question