set up an If Then function

[fitz13]

Can someone help me set this up: I want to divide a decimal by 2, then put results in each of two columns with three decimal places each. I cannot get the results to correctly accomodate three decimal places. For example: divide 10.325 in half and put 10.163 in one column and 10.162 in the other column. I don't want to carry out to four decimal places.

 

[rhysiam]

are you using microsoft excel?

 

[fitz13]

yes, version 14.0.7128.5000. Office 2010. Thank you.

 

[USAFRet]

yes, version 14.0.7128.5000. Office 2010. Thank you.

Limit your cells/columns to 3 decimal places.
Separate off the .xxx
Divide by two
Subtract that from the original decimal

 

[rhysiam]

Ifthe source value (the one you list as "10.365") will always have just three decimal places, the following will work:

SOURCE: 10.365
HIDDEN: =A1-ROUNDDOWN(A1,3) **This is a calcluation cell that you can hide later
VALUE1: =ROUND(A2/2,3)
VALUE2: =A2-A3

Obviously you'll need to change "A1" to your source number, "A2" to your calculation cell, and "A3" to the cell containing Value1

This will NOT work if your source value has more than 3 digits.

I could do this for you without the calculation cell, it's just messier, but I can give you the formula if you want.

 

[rhysiam]

yes, version 14.0.7128.5000. Office 2010. Thank you.

Limit your cells/columns to 3 decimal places.
Separate off the .xxx
Divide by two
Subtract that from the original decimal

That won't actually do what OP is asking, unfortunately. When you limit cells to a certain number of decimal places Excel will round the number for viewing purposes, but will base any calculations on the ACTUAL value, not the displayed value. In almost every situation that's exactly what you want it do.

In OP's example he would get 0.183 displayed in each cell, because the calculations are based on the actual value of 0.1825... then rounded... rather than rounded then calculated.... if that makes sense?

 

[USAFRet]

yes, version 14.0.7128.5000. Office 2010. Thank you.

Limit your cells/columns to 3 decimal places.
Separate off the .xxx
Divide by two
Subtract that from the original decimal

That won't actually do what OP is asking, unfortunately. When you limit cells to a certain number of decimal places Excel will round the number for viewing purposes, but will base any calculations on the ACTUAL value, not the displayed value. In almost every situation that's exactly what you want it do.

In OP's example he would get 0.183 displayed in each cell, because the calculations are based on the actual value of 0.1825... then rounded... rather than rounded then calculated.... if that makes sense?

True. The actual decimal is what would be calculated on. Then just shown as .xxx.