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

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)

=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

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

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

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.

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.9if 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

Re: I would like to add the rounding function into the True value of that if statementThat's exactly what I offered in Response 1 and explained Response 3.

Have you tried my suggestion?

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

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.

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

Ask Your Question

Weekly Poll

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History