Whole number as decimal

bohica48

New Member
Joined
Jul 23, 2008
Messages
4
I would like to enter whole numbers but have them convert to decimal. I have searched and found a solution, but it only references to one column and I need to reference other columns as well. I tried to edit but I’m not very knowledgeable with code. Here is an example of what I am looking for, columns E31:E52, F31:F52, L31:L52, M31:M52, N31:N52. Could someone provide a code to acquire these results? Thanks in advance.
<o:p> </o:p>
Excel version 2002
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
So you want a number like "102954" to convert to "0.102954"?

If so, =VALUE("."&A1) Assuming your data begins in A1.
 
Upvote 0
Welcome to the board.

Just format (format --> cells --> number) the desired cells as numbers with however many decimal places.
 
Upvote 0
If you're after Keith's suggestion, you can use tools --> options --> edit to set "fixed decimal places" to 6. Then, entering 102954 will store the value as 0.102954 in the cell.

Just turn "fixed decimal places" off when you're done, as the setting applies to every sheet on all open workbooks.
 
Upvote 0
Sorry for not being clear on the outcome. I need this for specific columns as others require actual whole numbers.

Example:
enter value of 45
Outcome as 0.45

The reason I want to be able to do this is not having to put a decimal for each entry, in the end I average each column. Hope this helps.

Thanks again,
 
Upvote 0
I believe that any suggestion above will work for you. With mine, you have to make an extra column and use the formula. Then if you want to get rid of the column, you have to copy > paste special (values) over what you entered.
 
Upvote 0
I used the code below which I found here, it worked for a particular defined column but I could not get it to work with multiple columns as I mentioned in my original post.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)</pre>
<o:p> </o:p></pre>
Application.EnableEvents = False</pre>
</pre>
Dim isect As Range</pre>
Set isect = Intersect(Target, Range("E1:E65536"))</pre>
If Not (isect Is Nothing) And IsNumeric(Target.Value) Then</pre>
Target = "0." & Target</pre>
End If</pre>
</pre>
Application.EnableEvents = True</pre>
</pre>
End Sub
</pre>
 
Upvote 0
Change this line to
Code:
Set isect = Intersect(Target, Range("E31:E52, F31:F52, L31:L52, M31:M52, N31:N52"))
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top