want half decimal value in cell

[fassi]

hi,
I have some calculation values in cells e.g.
B1=7.10, C1=0.20 and D1=B1-C1 with answer 6.9 but I need the answer 6.4.
B2=6.01, C2=1.39 and D2=B2-C2 with answer 4.62 but I want 4.12 instead of 4.62

I need the cell formatting which shows the decimal value 50 not 100.
plz help

[MarvinP]

Hi fassi,

Try this formula in B1 for numbers in A1
=IF(A1-INT(A1)>0.5,A1-0.5,A1)

[fassi]

Hi fassi,

Try this formula in B1 for numbers in A1
=IF(A1-INT(A1)>0.5,A1-0.5,A1)

thanks MarvinP
I have a large no of cells which contain such values e.g 5.99, 6.75, 5.59 etc etc
Is there any possibility to format these cells to get required values

[MarvinP]

Formatting the individual cells isn't the answer.
You will need to put the above formula in a separate cell to calculate the answer.

[fassi]

Formatting the individual cells isn't the answer.
You will need to put the above formula in a separate cell to calculate the answer.

MarvinP
I mean if I format all cells (Sheet) with any custom formula in formatting option because I want this option of half decimal for all cell of sheet. Your formula is very good but it is very difficult to change all cells in new cells because value of these cells is coming from other sheet.

[MarvinP]

Hi fassi,

Let me see if I can explain this problem, as I see it. You have values in cells like 4.92. You want it to show 4.42 instead. You can NOT create a formula to change 4.92 into 4.42 in the cell itself as it will then give an error message of Circular Reference. SO - you want to FORMAT the cell to show 4.42 insead of 4.92.

The above would take a formula. Formatting the cell to another type of display format will not change the value of the cell.

Why don't you create a new sheet that has my formula above but points back to the sheet with all the numbers on it. See the example.

[fassi]

Hi fassi,

Let me see if I can explain this problem, as I see it. You have values in cells like 4.92. You want it to show 4.42 instead. You can NOT create a formula to change 4.92 into 4.42 in the cell itself as it will then give an error message of Circular Reference. SO - you want to FORMAT the cell to show 4.42 insead of 4.92.

The above would take a formula. Formatting the cell to another type of display format will not change the value of the cell.

Why don't you create a new sheet that has my formula above but points back to the sheet with all the numbers on it. See the example.

hi MarvinP
Perfect n thanks