Rounding Function

[abacon1960]

I have a spreadsheet that is calculating a price per square foot of a building. The cell is formatted to Accounting with 2 decimal points.

This is what is should be based on my calculator tape
52,000 sf x $61.68 = $3,207,360

This is what the cell multiplication tells me
52,000 x $61.68 = $3,207,149.40

If you take the price per sqft manually to 5 decimal points ($61.67595) you get the correct amount if I multiply on my calculator.

What am I missing here?

Thank you!

 

[Joe4]

Where is that $61.68 coming from?
Is that some sort of calculated field?

Note that changing the formatting only changes what is displayed, NOT what is stored or used for calculations.
If you want that, then you need to round off the value used in your multiplication, i.e.
if sf is in cell A1 and price is in cell B1, then:

=A1 * ROUND(B1,2)

 

[abacon1960]

Here is what I have in Excel and then I will show you manually via calculator what I came up with.


EXCEL
Cell = A2/A3 = 2,467,038/40,000 = 61.67595 (Cell A4) (cell set to 2 decimal points and rounds to 61.68

Cell = A10 * A4 = New Amount = 52,000 * 61.68 = $3,207,149.40


MANUALLLY
If you manually calculate via calculator 52,000 * 61.68 I GET $3,207,360

If I manually take 61.68 to 5 decimals as Excel does, it becomes 61.67595
52,000 * 61.67595 = $3,207,149.4

Where does the 210.60 difference come from? No rounding has been assigned.

 

[jtakw]

Hi,

Although you have A4 formatted to show Only 2 decimal places, it Does Not mean Excel had Rounded the value, the underlying number is still 61.67595, you Told Excel to Only let you See 61.68.

If you want the accuracy to extend to Only 2 decimal places, you Need to Round the result of A2/A3:

Book1
	A	B	C	D	E
2	2467038
3	40000
4	61.67595	A4 not rounded		3207149.40	result using A4
5	61.68	A5 rounded		3207360.00	result using A5
6
7
8
9
10	52000
Sheet74

Cell Formulas
Range	Formula
A4	=A2/A3
A5	=ROUND(A2/A3,2)
D4	=A10*A4
D5	=A10*A5

 

[abacon1960]

Perfect! Works like a charm. Thank you!

 

[jtakw]

You're welcome.