# Solved: Roundup nested if in excel



## midawnss2 (Dec 5, 2008)

=IF(B7=B$2,(I7-H7)*(B$3),IF(B7=D$2,(I7-H7)*(D$3),IF(B7=C$2,(I7-H7)*(C$3))))

the above is my nested if function. This works to get the proper answer, but I need it to roundup. According to my instructor I should 
=ROUNDUP(IF

of course when I do this it does not work. Am I supposed to put other stuff with it. When I read about roundup it shows it like =ROUNDUP(A1,0) to round up column A1 to zero decimals. 

If I need to add anything please show me exactly where to add it and if it needs any , ( ) or anything else. 

I am rather frustrated with this, it is supposed to be simple but it is not for me. 

DAWN


----------



## cwwozniak (Nov 29, 2005)

Hi Dawn, and welcome to TSG.

Maybe your instructor meant ...

=ROUNDUP(IF(B7=B$2,(I7-H7)*(B$3),IF(B7=D$2,(I7-H7)*(D$3),IF(B7=C$2,(I7-H7)*(C$3)))),0)

This will round up the result to zero places. Change the zero at the end if you need a different number of decimal places.


----------



## midawnss2 (Dec 5, 2008)

I copy and pasted that exact formula and the excell program says that I have entered too many arguments. 

Still is not working. 

I even tried taking away a ) and that did not work either. 

Dawn


----------



## cwwozniak (Nov 29, 2005)

Strange.

I am using MS-Excel 2003 and just opened a new blank workbook. I then copied the line as listed in my TSG post and pasted it into cell A7. I did not get any error messages about the formula and cell A7 is showing a value of 0. All of the remaining cells are blank.

I can check it again to see if the error only appears for certain cell values. Just let me know what you have in all of the other referenced cells.


----------



## The Villan (Feb 20, 2006)

>>=IF(B7=B$2,(I7-H7)*(B$3),IF(B7=D$2,(I7-H7)*(D$3),IF(B7=C$2,(I7-H7)*(C$3))))<<

I suggest you need to do

=IF(B7=B$2,Roundup((I7-H7)*(B$3),0),IF(B7=D$2,Roundup((I7-H7)*(D$3),0),roundup((I7-H7)*(C$3),0)))

The roundup goes inside the If statement. If there are 3 results possible, then 2 require an IF() and within that ROUNDUP(). The last result does not need an IF statement as once you have sorted the first 2 results out, everything else has to be the 3rd result which still needs a ROUNDUP().

Hope that makes sense.

I haven't tested it, just typed it from the head as such, but the syntax is basically correct.


----------



## The Villan (Feb 20, 2006)

So the syntax is

=if(Test what you are looking for and if that is true then,Roundup(formula,decimal places),if(Test what you are looking for and if that is true then,Roundup(formula,decimal places),Roundup(formula,decimal places)))


----------



## cwwozniak (Nov 29, 2005)

The Villan said:


> The last result does not need an IF statement as once you have sorted the first 2 results out, everything else has to be the 3rd result




That may not be the desired result though. Dawn's original formula would return a value of zero if none of the three test conditions were true.


----------



## The Villan (Feb 20, 2006)

She would need 4 conditions in that case. the first 3 would need if(roundup() and the fourth wouldn't need an IF statement.

Basically that would be test the first 3 condirions and if they are true then carry out the calculation and the fourth has to be - anything else which doesn't need an if.

Syntax

IF(logical_test,value_if_true,value_if_false)

Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.

Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.

Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.


----------



## The Villan (Feb 20, 2006)

>>=IF(B7=B$2,(I7-H7)*(B$3),IF(B7=D$2,(I7-H7)*(D$3),IF(B7=C$2,(I7-H7)*(C$3))))
<<

which should have the following formula
=IF(B7=B$2,Roundup((I7-H7)*(B$3),0),IF(B7=D$2,Roundup((I7-H7)*(D$3),0),roundup((I7-H7)*(C$3),0)))

Broken down


=IF(B7=B$2,Roundup((I7-H7)*(B$3),0), <- the 0 is the number of decimal places to round to

IF(B7=D$2,Roundup((I7-H7)*(D$3),0), <- the 0 is the number of decimal places to round to

roundup((I7-H7)*(C$3),0) <- the 0 is the number of decimal places to round to

)) <- close off the 2 IF statements



The way I see it, she has 3 results in her formula, therefore the first 2 need to be tested and require an IF statement, the remaing calculation should occur, if the other 2 are FALSE and does not need an IF statement.

If you want to do it your way, you would need

=IF(B7=B$2,Roundup((I7-H7)*(B$3),0),IF(B7=D$2,Roundup((I7-H7)*(D$3),0),IF(roundup((I7-H7)*(C$3),0),0)))

However you have now introduced a 4th possibility that should return 0

That wasn't in Dawn's formula. Maybe she did want 0 to be returned if the other 3 were false.


----------



## midawnss2 (Dec 5, 2008)

Thank you guys so much. I was extremely frustrated and I tried everything each of you told me and it finally worked. I had to change a few other columns and then the computer would not let me get back on this web site until now. I am so relieved to have this finished.

I will have more excel work next Tuesday and will contact you I am sure. 
Thank you so much. 
Dawn


----------



## The Villan (Feb 20, 2006)

Dawn
You are very welcome.
Would you be kind enough to go to the top of this thread and click on the SOLVED button. It saves people spending their time on this issue when it has been solved.

If you need further help, then please start a new thread.

Les


----------

