# Solved: Roundup within a if, product formula

Discussion in 'Business Applications' started by Mcullin, Nov 9, 2011.

Not open for further replies.
1. ### McullinThread Starter

Joined:
Nov 9, 2011
Messages:
7
=IF((PRODUCT(C6*B7)/144)<10, PRODUCT((C6*B7)/144*3.84+10)*1.3, PRODUCT(C6*B7)/144*3.84*1.3)

I need to figure out how to get this to round up the (C6*B7)/144) to the 0 decimal.

example would be 36"*15"=540/144=3.75 (I need 3.75 to roundup to 4) but let the rest of the formula continue. If less than 10 sq ft and \$10 fee *1.3. If 10 sq ft or more then *1.3 only.

Any help would be greatly appreciated.
Thanks,
Michael

2. ### Rockn

Joined:
Jul 29, 2001
Messages:
21,334
Use the ROUND function and the number of decimal points.

3. ### McullinThread Starter

Joined:
Nov 9, 2011
Messages:
7
Using the round or roundup is kinda of obvious, my problem is where? do I replace Product?

4. ### Rockn

Joined:
Jul 29, 2001
Messages:
21,334
If(Round(Product(C6*B7)/144),0)<10

Something like that

5. ### McullinThread Starter

Joined:
Nov 9, 2011
Messages:
7
=IF(ROUND(PRODUCT(D4*B5/144),0)<10, PRODUCT((D4*B5)/144*3.84+10)*1.3, PRODUCT(D4*B5)/144*3.84*1.3)
Didn't work on rounding up to the next whole square foot. But at least this time it did not give any kind of error either.

6. ### Rockn

Joined:
Jul 29, 2001
Messages:
21,334
Put the function alone into a cell and see if it is rounding correctly.

7. ### McullinThread Starter

Joined:
Nov 9, 2011
Messages:
7
=ROUND(((D4*B5)/144),0) worked at rounding up but the second I add the product or if it stops working

8. ### Rockn

Joined:
Jul 29, 2001
Messages:
21,334
The product function is used to multiply values of cells. PRODUCT(A1,A2,etc)

You are already doing that with C6*B7 so you do not need the PRODUCT function at all.

9. ### McullinThread Starter

Joined:
Nov 9, 2011
Messages:
7
Okay I got that the product does not need to be there for the short round up only formula, but what about a more complex one.
=IF(ROUND((D4*B5/144),0)<10,((D4*B5)/144*3.84+10)*1.3,(D4*B5)/144*3.84*1.3). This still calculates without the round up. or
=IF(ROUND((D4*B5/144),0)<10,product((D4*B5)/144*3.84+10)*1.3,product(D4*B5)/144*3.84*1.3)
neither of these are rounding up the sq ft to the next whole number and then calculating the rest of the formula. Even though the short round up formula does work to roundup only.
I do appreciate your patience and assistance and sorry if I am not communicatiing what it is I need.
Michael

10. ### Rockn

Joined:
Jul 29, 2001
Messages:
21,334
You will need to add the round function to each of the instances within the If function.

=IF(ROUND((D4*B5/144),0)<10,Round(((D4*B5)/144*3.84+10)*1.3),0),Round((D4*B5)/144*3.84*1.3),0)

I am not sure I put it in the correct place for your usage, but you should get the idea.

11. ### McullinThread Starter

Joined:
Nov 9, 2011
Messages:
7
=IF((ROUND((D4*B5/144),0)<10,Round(((D4*B5)/144*3.84+10)*1.3),0),Round((D4*B5)/144*3.84*1.3),0)
This gives an error message as too few arguments. i think I tried this one yesterday night.

12. ### Rockn

Joined:
Jul 29, 2001
Messages:
21,334
You might be missing a round bracket at the end

13. ### Rockn

Joined:
Jul 29, 2001
Messages:
21,334
=if(round((d4*b5/144),0)<10,round(((((d4*b5)/144)*3.84)+10)*1.3,0),round((((d4*b5)/144)*3.84)*1.3,0))

14. ### McullinThread Starter

Joined:
Nov 9, 2011
Messages:
7
Finally got it. I appreciate the help, it was invaluble.

=IF(ROUNDUP(C4*B5/144,0)<10,(ROUNDUP(C4*B5/144,0)*3.84+10)*1.3,(ROUNDUP(C4*B5/144,0)*3.84*1.3))

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!