How do I make Excel add up in the way I would like?
in [UK Mac]
|
Prev: Works outing '09
Next: new to macs...a newsreader q..
From: Sara on 23 Oct 2009 10:22 I knock up the company invoices in Excel. I like doing it like this and don't want to use anything else. But one thing bugs me, a lot of the figures I use are the result of various addings, multiplications and divisions. Even a few subtractions. This means that even though they are displayed and printed with only two decimal places showing, some of the numbers have many more and appear to add up incorrectly at the bottom. F'rinstance, a column of figures like these: 10.234 10.0111114446 15.244 Total: 35.48911144 Will display as: 10.23 10.01 15.24 Total: 35.49 Which leads to customers (quite rightly) complaining that their bills don't add up. Is there a way to make Excel just add up the numbers as displayed? -- Sara Cuddler of rats, cats and husband
From: Chris Ridd on 23 Oct 2009 10:38 On 2009-10-23 15:22:26 +0100, Sara <saramerriman(a)blueyonder.co.uk> said: > I knock up the company invoices in Excel. I like doing it like this and > don't want to use anything else. But one thing bugs me, a lot of the > figures I use are the result of various addings, multiplications and > divisions. Even a few subtractions. This means that even though they are > displayed and printed with only two decimal places showing, some of the > numbers have many more and appear to add up incorrectly at the bottom. > > F'rinstance, a column of figures like these: > > 10.234 > 10.0111114446 > 15.244 > > Total: 35.48911144 > > Will display as: > > 10.23 > 10.01 > 15.24 > > Total: 35.49 > > Which leads to customers (quite rightly) complaining that their bills > don't add up. > > Is there a way to make Excel just add up the numbers as displayed? Add an extra column which does a ROUND() on the input numbers with 2 decimal places, then do the SUM on the ROUNDed numbers? Or if you want to avoid the extra column just modify the field doing the SUM to call ROUND on each of its arguments, eg: =SUM(ROUND(A1,2),ROUND(A2,2),ROUND(A3,2)) You'd need to make sure ROUND() does what formatting as "currency" does. There are some unusual rounding algorithms, er, around. -- Chris
From: Chris Ridd on 23 Oct 2009 10:42 On 2009-10-23 15:38:57 +0100, Chris Ridd <chrisridd(a)mac.com> said: > On 2009-10-23 15:22:26 +0100, Sara <saramerriman(a)blueyonder.co.uk> said: >> Is there a way to make Excel just add up the numbers as displayed? Another way: Set Preferences > Calculation > Precision as displayed. Apparently that throws away any non-displayed precision in the whole worksheet and you cannot get it back, so it looks kind of risky. -- Chris
From: David Sankey on 23 Oct 2009 10:49 In article <saramerriman-544A7C.15222623102009(a)news.individual.net>, Sara <saramerriman(a)blueyonder.co.uk> wrote: > I knock up the company invoices in Excel. I like doing it like this and > don't want to use anything else. But one thing bugs me, a lot of the > figures I use are the result of various addings, multiplications and > divisions. Even a few subtractions. This means that even though they are > displayed and printed with only two decimal places showing, some of the > numbers have many more and appear to add up incorrectly at the bottom. > > F'rinstance, a column of figures like these: > > 10.234 > 10.0111114446 > 15.244 > > Total: 35.48911144 > > Will display as: > > 10.23 > 10.01 > 15.24 > > Total: 35.49 > > Which leads to customers (quite rightly) complaining that their bills > don't add up. > > Is there a way to make Excel just add up the numbers as displayed? Use "ROUND" in the calculations for the numbers =ROUND(calc,2) 'scuse the formatting: Number =A2 =ROUND(A2,2) 10.234 10.23 10.23 10.01111144 10.01 10.01 15.244 15.24 15.24 35.48911144 35.49 35.48 Kind regards, Dave
From: Sara on 23 Oct 2009 11:35
In article <7kdtg1F39ia55U1(a)mid.individual.net>, Chris Ridd <chrisridd(a)mac.com> wrote: > On 2009-10-23 15:22:26 +0100, Sara <saramerriman(a)blueyonder.co.uk> said: > > > I knock up the company invoices in Excel. I like doing it like this and > > don't want to use anything else. But one thing bugs me, a lot of the > > figures I use are the result of various addings, multiplications and > > divisions. Even a few subtractions. This means that even though they are > > displayed and printed with only two decimal places showing, some of the > > numbers have many more and appear to add up incorrectly at the bottom. > > > > F'rinstance, a column of figures like these: > > > > 10.234 > > 10.0111114446 > > 15.244 > > > > Total: 35.48911144 > > > > Will display as: > > > > 10.23 > > 10.01 > > 15.24 > > > > Total: 35.49 > > > > Which leads to customers (quite rightly) complaining that their bills > > don't add up. > > > > Is there a way to make Excel just add up the numbers as displayed? > > Add an extra column which does a ROUND() on the input numbers with 2 > decimal places, then do the SUM on the ROUNDed numbers? Or if you want > to avoid the extra column just modify the field doing the SUM to call > ROUND on each of its arguments, eg: > > =SUM(ROUND(A1,2),ROUND(A2,2),ROUND(A3,2)) > > You'd need to make sure ROUND() does what formatting as "currency" > does. There are some unusual rounding algorithms, er, around. ooo I didn't know about that. -- Sara Cuddler of rats, cats and husband |