Results 1 to 9 of 9

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Excel has a ROUND() function....

    Try something like:

    =ROUND(A2,2)*B2

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


  3. #3
    That did it! Thanks NBVC!!!

  4. #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. #5
    This is what i was using: =IF(E16="","",E16*B16) where e is the cost and b is the quantity.

  6. #6
    Good afternoon,

    Wrap an IFERROR around it.

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

  7. #7
    Quote Originally Posted by bgoree09 View Post
    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.
    Last edited by maniac; 2014-10-23 at 08:58 PM.

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    So this doesn't work?

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


  9. #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
  •