# Thread: My invoice line totals are out because of lack of cell rounding....help!

1. ## My invoice line totals are out because of lack of cell rounding....help!

I have an invoice set up in excel using vlookup. Everything seems to be fine except I noticed that my selling price (cost + %) is out when I sell multiple quantities of certain items. What I've figured out is the selling cost cell (set to currency and 2 decimal places) actually has more then 2 decimal place for some items (ie. \$16.37 is actually \$16.367). My line total uses the \$16.367 instead of the rounded \$16.37, therefore my totals are out for the total invoice amount. Is there a way to have Excel use the rounded amounts instead of the expanded amounts? I want the invoice to read 2 X \$16.37 = \$32.74, not 2 X \$16.37 = \$32.73 because Excel doesn't account for the rounded up cost!

Any help would be appreciated!

2. Excel has a ROUND() function....

Try something like:

=ROUND(A2,2)*B2

where A2 contains the actual amount and B2 contains the qty.

3. That did it! Thanks NBVC!!!

4. Only issue now is i get a return of #VALUE! in all my line totals on the invoice. How do you return it so it is just blank?

5. This is what i was using: =IF(E16="","",E16*B16) where e is the cost and b is the quantity.

6. Good afternoon,

Wrap an IFERROR around it.

= IFERROR ( -- Formula -- , "" [or 0, whichever] )

7. Originally Posted by bgoree09
Good afternoon,

Wrap an IFERROR around it.

= IFERROR ( -- Formula -- , "" [or 0, whichever] )
So this is what I'm trying and it doesn't seem to work =IFERROR(ROUND(E16,2)*B16,"") Am I missing something?

I may need to clarify.... What NVBC suggested worked for my rounding issue. However, that formula returns #VALUE! on my invoice line total for any blank lines. This is what I was using prior to NVBC's recommendation =IF(E16="","",E16*B16). I'm just trying now to get =ROUND(E16,2)*B16 to return a blank cell. Hope this clears things up.

8. So this doesn't work?

=IF(E16="","",ROUND(E16,2)*B16)

9. Again, that worked perfectly, thank you! I appreciate your patience and help.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•