Calculations and Decimal Places

[claire publishing]

Hello

Sorry if this is a very basic problem and myself and nobody in my office can work this out.

I am wanting to know how I can make excel do its calculations based on the numbers as the appear on the screen (to the number of decimal places that I have selected in format cells) not the number of decimal places that may have been entered or generated.

For example:

How do I make

$17.156
+ $18.156
(which would appear as $17.16 + $18.16)
calculate to $35.32 not $35.31

Hope this makes sense? Any help would be most appreciated.

Claire

 

[Tazguy37]

Welcome to the Board!

You could use the CEILING() function:

=CEILING(17.156+18.156,0.01)

or use ranges instead of hardcoding the numbers. Hope that helps!

 

[claire publishing]

Thanks TazGuy. Any suggestions how I get this function to apply to a whole work sheet?

 

[Tazguy37]

Depends how your data is laid out. Here's an example:

Book1
	A	B	C	D
1	0.203682	0.7989	1.01
2	0.286996	0.904193	1.2
3	0.25086	0.70594	0.96
4	0.883056	0.77056	1.66
5	0.721979	0.817688	1.54
6	0.54186	0.281064	0.83
7	0.417989	0.854109	1.28
8	0.114209	0.987975	1.11
9	0.201281	0.156195	0.36
10	0.510126	0.880185	1.4
11	0.836948	0.894575	1.74
12	0.362965	0.369428	0.74
13	0.125767	0.384881	0.52
14	0.889947	0.04986	0.94
15	0.352188	0.007651	0.36
16	0.031544	0.221446	0.26
17	0.933104	0.743888	1.68
Sheet2

Formula in C1 (copied down) is:
=CEILING(A1+B1,0.01)

See how that's working? Post back if you have more questions.

 

[Aladin Akyurek]

Check the option Precision As Displayed on the Calculation tab under Tools|Options.