Summing with displayed values rather than absolute ones-help

[Barbarte]

Hi Guys,

I'm having a problem with invoices.
we issue invoices to people, obviously rounded up to the nearest cent.
however, even though the values appear to be rounded up, in fact they are not. and excel still makes calculations on the un-rounded value.
this is fine and dandy for one or two products, but once sales go above a certain amount things don't add up any more and it causes problems like below:

Book1
	A	B	C	D
1
2		1.50	1.50
3		1.50	1.50
4		1.50	1.50
5		1.50	1.50
6		1.50	1.50
7		1.50	1.50
8		1.50	1.50
9		1.50	1.50
10		1.50	1.50
11		1.50	1.50
12
13	Total:	15.04	15.00
14
Sheet1

Is there a way to Sum so that excel takes the rounded figure as the figure to sum rather than the absolute figure behind whats displayed in the cell??

thanks,

Paul.

Oh yeah, in the example above the cells are rounded to two decmal places, the actual value is €1.504 I want excel to sum the displayed value instead of that one.

 

[BJungheim]

Try:

=SUM(ROUND(B2:B12,2))

Entered with CTRL+SHIFT+ENTER

 

[Richard Schollar]

Hi Paul

Wouldn't you be best off to ensure the invoice value is actually rounded to 2 dp in the first place before you come to do any sum calculations on these values? Otherwise you're compensating for errors in your source data (no customer could pay you €0.004 for example). Just my thoughts.

 

[Barbarte]

cool!

thanks,

so if I wanted to round to 4 places would it be:


=SUM(ROUND(B2:B12,4))


also, what does Ctrl+Shift+Enter do to make that work?

thanks,

 

[Barbarte]

Hi Paul

Wouldn't you be best off to ensure the invoice value is actually rounded to 2 dp in the first place before you come to do any sum calculations on these values? Otherwise you're compensating for errors in your source data (no customer could pay you €0.004 for example). Just my thoughts.

Yep, in an ideal workd I would.
but I deal with music royalties which are all worked out as % of dealer prices which run to multiple decemal places.
Can't I use the above formula to round the royalty calculations when I'm summing them in the first place.
then from then on it shall all be in order?

 

[lenze]

Tools>Options>Calculation Tab. Select "Precision as displayed"

lenze

 

[Richard Schollar]

The way I would approach it is that you can't invoice a value with more than 2 decimal places, so the invoice must be rounded to 2 dp. However, the invoice could be made up of 10 items (which might be values to 6 decimal places, for example). If you don't want these 10 items rounded before they are summed (and hence invoiced) then you could use:

=ROUNDUP(SUM(A1:A10),2)

However, if you wanted them rounded up before they are summed, you could either incorporate the ROUND within the formula that is working out the royalty ie:

=ROUNDUP(CashValue*Royalty%,2)

or you could, as BJungheim advised, use:

=SUM(ROUNDUP(A1:A10,2))

(confirmed with Ctrl+Shift+Enter).

My personal preference in this second instance would be for rounding every calculated line (ie rather than the array formula) because it is plainly clear what is being rounded. However it is a personal preference thing.

 

[Barbarte]

Thanks everyone, I think I've got a handle on it now.
I will round up on a line by line basis and the rest will sort itself out.

Tools>Options>Calculation Tab. Select "Precision as displayed"

lenze

That idea is too extreme as it corrupts the initial data.

Thanks for all your help.