I am struggling to make this formula work. It seems to round up to 1 or 0, but not to 2 or 3. I would really appreciate any advice.
=IF(B29<1,0,ROUNDUP(((B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35)>0,0))
![]() |
I am struggling to make this formula work. It seems to round up to 1 or 0, but not to 2 or 3. I would really appreciate any advice.
=IF(B29<1,0,ROUNDUP(((B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35)>0,0))
What are the values in the cells?
Also can we assume you are using Excel? If so, which version? If not, what spreadsheet?
Hi Scottgem, thank you for replying so quickly. I really appreciate the advice.
I'm using Excel 2003.
The values in the cells
=IF(B29<1,0,ROUNDUP(((B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35)>0,0))
=IF(2340<1,0,ROUNDUP(((2340/20/4/20)-1)>0,0))
All I want the ROUNDUP function to do is to make the current cell (of the calculation answer) roundup anything with a decimal to a full number. The formula above works for 1 and 0, but not 2 or 3. It even works for negatives.
Please help...
Try this:
=IF(B29<1,0,ROUNDUP(((B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35),0))
I don't know why you had the >0 in there. But what that does is return either a 0 or 1, not the results of the arithmetic.
The only problem with this calculation is that it produces negative rounded numbers. I would like the negative numbers to round up to 0 (not -1). Do you know of a solution?
Thanks so much for your help.
Then you need to nest an IF:
=IF(B29<1,0,IF(((B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35)<0,0,Roundup(((B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35))))
So, If B29 is <1, you get a 0. Otherwise it does the calculation. If the result of the calculation is less than 0, then it returns a 0 otherwise it returns the rounded calc.
Maybe you can employ the MAX function to make sure it never goes below 0.
=Roundup(B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35)
=MAX(0, Roundup(B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35))
All times are GMT -7. The time now is 01:20 PM. |