ms-excel: extract decimal and integer part of a number and disable fields changing
I have a numeric field (say A). I have to extract its integer part in another field (say B) and its decimal part in another field (say C). I would that the user can see the values in B and C cells, when changing A cells, but at the same time I don't want that the user can change the values in B and C cells.
Is it possible?
Let's say you have 10.6 in cell A1. From there, do this:
- in Cell B1, enter =INT(A1). This will show 10.
in Cell C1, enter =A1 - INT(A1) This will show 0.6.
Alternately, based on your examples, if you want 0.6 to show as 6, or 0.56 to show as 56, use this in cell C1:
=(A1 - B1) * (10 ^ (LEN(ROUND(A1-B1, 2))-2))
I ROUND to two decimal places above, because when you can get weird lengths because of floating point calculations (try LEN(8.8-8) for example).
Copy the formulas in B1 and C1 as far as you want.
Unlock all your cells by selecting them all, right-clicking the form and selecting "Format Cells...", clicking on the Protection tab, and unchecking "Locked".
- Right-click the range you want to lock (B and c), right-click on them again, go back and select "Lock"
- Go to Tools > Protection and Protect Worksheet