PDA

View Full Version : Excel folks, why is my grand total off by a penny or two, rounding issue or ???



applecrisp
03-03-2008, 12:18 PM
I am having a problem with some invoicing, some of the invoices are off by a penny or two. It's must be something with the rounding. How I "word" my calculation? How the cell is formatted?

Any advice would be fantastic and much appreciated.

Thanks,

Here's an example of an invoice (has cost per thousand, base cost, plus other costs). The number of widgets is 5001. It's 150/M for the items, plus 15/M extra for color etc:

5001 Widgets $150.00 /M total is 750.15
5001 Color $15.00 /M total is 75.02
5001 Size $10.00 /M total is 50.01
5001 Shape $15.00 /M total is 75.02

1 Shipping $65.00 F total is 65.00

GRAND TOTAL $1,015.19 but I manually get 1015.20, a penny more than what Excel calculates.

To calculate cost of item (to get the $750.15, I do..... =A29*F29/1000). The total goes into "H" column. "A" is the qty and "F" is the cost per thousand.

To calculate total I do.... =sum (H29:H34) ---there are some lines with no data.

To get the 1,015.20 I manually added 750.15+75.02+50.01+75.02

funniegrrl
03-03-2008, 12:27 PM
If I understand your equations correctly ...

When you do =A29*F29/1000, etc., these subtotals probably contain more decimal places than you are seeing. So, when you're adding them all up, you are getting a truer total that is rounded at the end, rather than adding up the visually rounded subtotals.

Edited to add ... you can probably see what's going on better by (at least temporarily) changing the formatting of your subtotal column so that you see 3 or 4 decimal places rather than just two. Then you'll see the more precise numbers that Excel is using to calculate the grand total.

Jewel
03-03-2008, 12:32 PM
Look at how the cell/column is formatted...right click on the cell, click on 'format cell' and make sure it's in the 'number' mode with the decimal in the correct place. If that doesn't work, put it in 'text' mode which types the numbers exactly as written.

Your formula looks right, but I'm NOT an Excel expert, so hopefully someone else will chime in! :o

MinEaston
03-03-2008, 01:54 PM
The rounding issue is coming into play on your second and fourth items, where you have an actual cost of 75.015 [(5001/1000)*15]. Using 2 decimals, Excel displays it rounded up to 75.02, but when Excel adds it for the total, it is adding using more decimals. So it looks funny.

I'm not sure how funny it would be to display 3 decimals on the invoice, for the detailed lines.

But, that's why Excel is showing what it's showing. I tried some testing of the "ROUND" function, but that didn't change the actual total of 1015.19.

Meganator
03-03-2008, 02:32 PM
The rounding issue is coming into play on your second and fourth items, where you have an actual cost of 75.015 [(5001/1000)*15]. Using 2 decimals, Excel displays it rounded up to 75.02, but when Excel adds it for the total, it is adding using more decimals. So it looks funny.

I'm not sure how funny it would be to display 3 decimals on the invoice, for the detailed lines.

But, that's why Excel is showing what it's showing. I tried some testing of the "ROUND" function, but that didn't change the actual total of 1015.19.

If you round each individual line (either by using the ROUND function in the formula you already have, or by using a separate column to show the rounded amount), then sum the rounded numbers, the total comes out to $1015.20. Note that the total should be just the SUM function WITHOUT rounding, otherwise you come out with $1015.19.

I don't think any customers expect you not to round currency to 2 decimal places, so that part isn't a problem.

applecrisp
03-03-2008, 02:35 PM
What do I type to use the round function? Can you tell I'm not an Excel pro. :D

Many thanks everyone.

BucknellAlum
03-03-2008, 03:06 PM
Maybe I am oversimplifying, but I don't think you need to round.

Are your cells formatted for currency, with 2 decimal places? I would think if that were the default format of the currency cells, then those items that were $75.015 would be automatically input as $75.02 and your total would be correct?

(I haven't actually done the math as I am on my way out the door, but I thought that was the whole purpose of setting a # of decimals in the format).

applecrisp
03-03-2008, 03:10 PM
Bucknell Alum.

The totals for each line, are set for number with 2 decimal places. The cost fields (ie $150/M) are set for currency with 2 decimal places.

Thanks,

Meganator
03-03-2008, 03:14 PM
What do I type to use the round function? Can you tell I'm not an Excel pro. :D

Many thanks everyone.

If you use a separate column, let's say I, for the rounded numbers, you will type into the column I cell the following:

=ROUND(H29,2)

That will take the amount in cell H29 and round it to 2 decimal places. Also, you will want to format that cell as currency with 2 decimal places (in the Format/Cell/Number menu). Then when you take the straight sum of the I column, it will be the same number you get when you manually add them.

You can achieve the same result without creating a whole other column (column I). You can change your H column formula, which is now =A29*F29/100 to be:

=ROUND(A29*F29/1000,2)

Then you get the already rounded number in column H.

Meganator
03-03-2008, 03:17 PM
Maybe I am oversimplifying, but I don't think you need to round.

Are your cells formatted for currency, with 2 decimal places? I would think if that were the default format of the currency cells, then those items that were $75.015 would be automatically input as $75.02 and your total would be correct?

(I haven't actually done the math as I am on my way out the door, but I thought that was the whole purpose of setting a # of decimals in the format).

The numbers that are SHOWN are rounded, but the sum is of the non-rounded numbers. So when you add the numbers as they appear you get a different number than what Excel calculates. What I didn't check was to see if you can just round the sum instead of the individual numbers, though.


Edited to add: I went and checked, and it doesn't work if you round the sum instead of the individual lines. If you just round the sum, it is 1015.19 still.

BucknellAlum
03-03-2008, 08:57 PM
The numbers that are SHOWN are rounded, but the sum is of the non-rounded numbers. So when you add the numbers as they appear you get a different number than what Excel calculates. What I didn't check was to see if you can just round the sum instead of the individual numbers, though.


Edited to add: I went and checked, and it doesn't work if you round the sum instead of the individual lines. If you just round the sum, it is 1015.19 still.

I knew I was oversimplifying!! :o

applecrisp
03-03-2008, 09:06 PM
Thanks everyone for the replies. And, Meganator ---- it worked with the =Round.

I can't tell you how long I have had to manually correct (and doublecheck) that my invoices were correct. THANK YOU soooo much. What a huge time saver.

So, Meganator ---just so I will understand the basis of the forrmula, by typing ..... =ROUND(A29*F29/1000,2) ------ when ultimately adding those figures, it is basically saying to use/refer the rounded formula and not to use what is "behind it". Ok, not describing it right. So is ", 2" indicate how many decimal places. Would it then be ",3" if for some reason I had the number displaced with three decimal places.

Thanks again. What an awesome place! You guys are great!

Meganator
03-04-2008, 09:48 AM
So, Meganator ---just so I will understand the basis of the forrmula, by typing ..... =ROUND(A29*F29/1000,2) ------ when ultimately adding those figures, it is basically saying to use/refer the rounded formula and not to use what is "behind it". Ok, not describing it right. So is ", 2" indicate how many decimal places. Would it then be ",3" if for some reason I had the number displaced with three decimal places.

Thanks again. What an awesome place! You guys are great!

Yes, you are understanding it correctly! :)

applecrisp
03-04-2008, 09:58 AM
A BB where you can get recipe for dinner and Excel help. What a place. I can't tell you how much time and hassles this has saved me. I just couldn't figure it out.

Thanks again for helping me out!