Calculating a displayed number

[clock245]

This is a dumb question, but i can't seem to figure it out. What i'm looking for is a formula (not vb) that will take the variance of 2 values "displayed values" and give me the number. So an example: cell a1 = 3.2; cell a2 = 6.6; cell a3 = a2-a1. However, i'm formatting all these cells to whole numbers without decimals. So a1 = 3 and a2 = 7 which makes a3 = 3. The value i'm looking for a3 to be is 4 based on the displayed values. Thank you in advance

 

[anandvarma]

TRY THIS

Excel Workbook
	A
1	3
2	7
3	4
Sheet1

 

[AlphaFrog]

There is a setting called Precision as displayed on Excel's Options dialog that will calculate based on the displayed values.

Excel 2007: Set rounding precision

Excel 2003: Precision as displayed

Alternativly, you could use the ROUND function to round the values. This gives you the desired result, but it's not tied to the diplay settings.

=ROUND(A2,0)-ROUND(A1,0)

 

[clock245]

sorry i should have been more clear...it has to work both ways (up and down)...but alphafrog thanks for the formula...sometimes missing the obvious is the hardest thing to figure out!