I have the value 26.06 in cell A1, I want to have everything left of the decimal place in Cell B1 and everything right of the decimal place in Cell C1, is this possible. Bearing in mind A1 will be variable (ie it could be 159.65).
View 9 RepliesI am trying to figure out how to insert a decimal into a number. For ex: 1234 to become 12.34. I tried creating custom or special formatting but I cannot figure it out.
View 3 Replies View Relatedhow to control this,it should be 0.55. is they a wat too round off too next highest hole number? Here is a example.
View 2 Replies View RelatedI have a column of percentages where I only want to show 1 decimal place if the value is less than 1%. The rest of the percentages I want without decimal detail.
I've tried custom formats and cant quiet get it right. Am I close with this?
[>1]#.#%;###%
I would like to be able to add one decimal place to each cell in my range.
Example:
111.111 becomes 111.1110
22.22 becomes 22.220
3.3 becomes 3.30
There are no standards here, I just want to be able to add that last decimal place. I need cells to remain in "Number" format also.
I am trying to found or create a formula that will display cell width to the decimal place.
I can use the formula Cell("width"), but it rounds to the nearest integer. I need to display the decimals. For instance, if the actual column width is 8.43, the Cell("Width") formula will display 8. I need it to show 8.43.
Is there a way that I can remove the whole number to leave the decimal place.
1.69 should be 0.69
10.71 should be 0.71
0.48 should be 0.48
How can I restrict entry into a textbox to 1 decimal place?
View 9 Replies View RelatedI have an UserForm, where I have several text boxes. One of these textboxes should be entered with decimals. I have been able to cope with the declarations, and set the variable as Variant. However, is it possible to prevent the user inserting "," instead of "."? Alternatively, automatically change "," to "."?
View 2 Replies View RelatedI am trying to export a range of data from excel to xml. I have a module that puts the range of data into one cell:
Code:
Function Concat(myRng As Range)
Dim myStr As String
Dim c As Range
myStr = ""
For Each c In myRng
If c.Value "" Then myStr = myStr & ", " & Chr(34) & c.Value & Chr(34)
Next
Concat = Mid(myStr, 2, 9999)
End Function
How to all data to come in with one decimal place (eg. "4.5","5.0",7.0")
Right now it looks like this:
("4.5","5",7")
I'm looking to see if there is a way to take the decimal place formatting of cell and apply it to other cells.
For example, if someone types in 0.0001 into a source cell, I'd like to take that decimal formatting and apply it to other destination cells. This way when values are typed into those cells it will automatically display 4 places past the decimal, no matter what the value. ie 50 will display 50.0000
Any function in Excel, that would allow me to have a unit cost price (for example: 0.5432) and then for a table of figures containing the pack sizes to determine the correct unit price that would allow all pack costs to be at 2 decimal places. I have included an example below:
My spreadsheet would look like the following:
Cell A1 (Unit Cost Price) = .5814
Cell A3 (Pack of 75 units) = .5814*75 = 43.605
Cell A4 (Pack of 80 units) = .5814*80 = 46.512
Cell A5 (Pack of 100 units) = .5814*100 = 58.14
and so on
I need some way of making cell A1 change to a value that will cause all cells A3 to A5 to be 2 decimal places or less.
I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.
View 2 Replies View RelatedI have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
I have a vba macro that takes data from one workbook and pastes it into another workbook. In doing this I have declared a few variables of type single (I only need two decimal precision). However, when I copy the values from the cells on the source workbook and paste them into the target workbook, the numbers end up having 12 decimal places. Ultimately, this extra precision causes my totals to be off by .01 or more after a while. I have tried rounding the number as I pull it off the source workbook into the variable, but that didn't matter. How do I solve this problem? Code for pulling data from source workbook:...
View 2 Replies View RelatedBasically, I want to format a group of cells to display 1 decimal figure if the number is not a whole number. If the number is a whole number (or if the rounded first decimal place is 0) I want it to display no decimal.
View 9 Replies View RelatedI need to convert a column of numbers currently formatted with 2 decimal places e.g. 112.12 to 4 decimal places (without the decimal point). I need the end result to be 1121200. I've tried a few different suggestions given on the forum previously but can't seem to retain the 4 decimal places that I require.
View 4 Replies View RelatedI have to make a excel document in which I have length and width in feet and inch format.
E.g. 10.01 in which 10 is feet and .01 is inch
I have all the length and width values in the above format. And what I want to do is convert the inch value (10.01) to feet value (.01=.08 feet) .
Just like the calculator here does.. [URL] ........
Like
10.01=10.08
10.02=10.16
10..03=10.25
...and so on...
Here is table of conversions from inches to decimal feet. But I don't know how to get a formula for this in excel...????
Inch Decimal of a Foot
1 inch 0.0833
2 inches 0.167
3 inches 0.250
4 inches 0.333
5 inches 0.417
6 inches 0.500
7 inches 0.583
8 inches 0.667
9 inches 0.750
10 inches 0.833
11 inches 0.917
12 inches 1.000
I am trying to create a unique sample code by putting together the values of other cells that a user will input. It's all working well apart from the last part, where I am trying to include a decimal number. I want the decimal number to appear without the central "." and in a four digit format. e.g. 2.5 would appear as 0250, 14.25 would appear as 1425. This is the formlua I am using currently:
=IF(ISBLANK(B4),"",IF(LEFT(C4,1)="w",(B4&"-"&TEXT(F4,"YYMMDD")&C4&TEXT(G4,"HHMM")),(B4&"-"&TEXT(F4,"YYMMDD")&C4&LEFT(TEXT(H4,"00"),2)&RIGHT(TEXT(H4,"00"),2))))
However, where the value of H4 is 2.5, I am getting a result of 0303 (I've put this part in bold). I have attached a small spreadsheet to aid understanding.
I need a formula to multiply only the decimal number in a cell and not the integer. For example: the number in the cell is 57.3615. I want to multiply .3615 only.
View 2 Replies View RelatedI need a formula to take a number with several decimal places and round it up to two decimal places to either .33, .66 or, .00 if its above .66.
For example, 4.23423423423423 will be 4.33
4.43453453533434 will be 4.66
4.8353453453 will be 5.00
Lets say the number is in cell A1. What formula would do this?
I need some formula how to sum data with different format (general & $ (currency), splitting summing,
check this below :
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
[Code] .....
If I have a varying number of figures(say between 4 and 10) which I want the average of the first half of the set and an average of the second half, is there a function which I can use to calculate this? ie if there is 8 numbers in total then I need the average of the first four and last four... but if there is ten figures total then I need the average of the first five and last five.
View 2 Replies View RelatedIf I have a string which contains 3 words, is there an easy way of separating out the consonants into one string and the vowels into another (spaces should be ignored)?
The only way I can think of doing it is to go through the string item by item and comparing the letters to a list of vowels and using that to do the separation.
I have text of varying length that I need to split up into three fields. I am using a vba code (kindly shown to me on a previous post) to split the first word, but in this particular macro I need to remove the last word. This is the code that I have at present which is displaying the first word.
Sub splitDescription()
Dim c As Range, t
For Each c In Range("e2:e" & Range("e" & Rows.Count).End(xlUp).Row)
t = Split(c)
c.Offset(, 1) = t(0)
Next
End Sub
The data I am trying to split consists of descriptions of varying lengths and I need to remove the first word and the last word and keep the middle text. Below is an example
SUEDE ROUCHED TRIM COURT GOLD
LEATHER/PATEN METALLIC SNAKE COURT BLACK
SUEDE RIBBONED BOW PURPLE
I have a To-do list of tasks in column A.
I want to split it up into assignments for different days of the week. I've labeled columns B thru F as Monday thru Friday and I want to split the list into blocks for those days.
Right now I have 54 items and I want my block size to be 12 items, which gives me 4 blocks of 12 and 1 block of 6 - can I make Excel do the splits for me?
The line below was originally a merged cell. I need to have the customer ID, Customer name, City, State and zip in seperate lines. I tried using the text to column, but the problem is some companies have a comma after their name to show if it is an llc or inc etc. What happens is the companies that do not have inc etc- it works just fine, however the ones that do have inc etc because there is another comma- it moves everything over one column. In the city column it says inc and in the state it has the city etc. Is there an easy way to make this work?
View 9 Replies View RelatedI have a spreadsheet I'm designing which requires me to take a series of numbers and return them lowest to highest. That's the easy bit.
The problem is where and how these numbers are displayed. They are not listed easily in two columns with the numbers in one column and someone's name against each one, but rather in four columns mapping out the odds and evens.
For example
1 Bob 2 Keith
3 Linda 4 Martin
Now obviously it'd be easiest if I could just re-format things into two columns, but that's not an option. However I'm finding myself a bit stuck when it comes to using an array in a formula.
I still want to return the names next to number, but I'm not sure how to have what's effectively a multiple array (or search two arrays at the same time). I'm looking to search the cells A1:A2 and C1:C2 in the example above, but not the cells B1:B2.
I just need to take the numbers in those cells A1:A2 and C1:C2 and list them in order highest to lowest.
How to split the data in my cells
Currently it looks like this.
1 - 2 - 3,4,5
A - B - C
I would like it to look like this.
1 - 2 - 3
1 - 2 - 4
1 - 2 - 5
A - B - C
Split the single into two, is this really possible in excel?To be precise dividing cell A1 into two different cells(No merging, No text to column option).
View 4 Replies View Related