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

maniac

New member
Joined
Oct 23, 2014
Messages
6
Reaction score
0
Points
0
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!
 
Excel has a ROUND() function....

Try something like:

=ROUND(A2,2)*B2

where A2 contains the actual amount and B2 contains the qty.
 
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?
 
This is what i was using: =IF(E16="","",E16*B16) where e is the cost and b is the quantity.
 
Good afternoon,

Wrap an IFERROR around it.

= IFERROR ( -- Formula -- , "" [or 0, whichever] )
 
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:
So this doesn't work?

=IF(E16="","",ROUND(E16,2)*B16)
 
Again, that worked perfectly, thank you! I appreciate your patience and help.
 
Back
Top