set up an If Then function

Status
Not open for further replies.

fitz13

Estimable
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.

Solution
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

Honorable
are you using microsoft excel?

fitz13

Estimable
yes, version 14.0.7128.5000. Office 2010. Thank you.

USAFRet

Illustrious
Moderator

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

rhysiam

Honorable
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

Honorable

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

Illustrious
Moderator

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

Status
Not open for further replies.

Replies
2
Views
5K
Replies
2
Views
2K
Replies
2
Views
4K
Replies
7
Views
2K
Replies
1
Views
2K