I need to control the amount of decimals allowed by the entry of another column same row so I do not have to format the cells everytime to get the correct decimal places. I need all my data in one table so I can do pivot tables.
View 14 RepliesI 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 have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
View 7 Replies View RelatedI have a scroll (form control) linked with data from another sheet. It scrolls nicely and works fine but I'd like to be able to update the data sheet and then the table with the scroll automatically update too.
View 9 Replies View RelatedCell C9: 1.25773
Cell C10: 20.0
Cell C11: 2.25%
Cell C15: =C9+((C10*C11)/10000)
C9 is a user entered value, currently formated general
C10 is a user entered value, currently formated decimal with one decimal
C11 is a user entered percentage, currently formated percent with two decimals
C15 is a formula calculation, currently formated general
I've tried formating cells to general (and/or) text and the values appear to be correct but still don't show what I want.
The most popular entries for C9 will be:
123.12
123.123
1.1234
1.12345
Any of the above could have one or more trailing zeros.
I would like C15 to show the same amount of decimal places that the user enters in C9. If user enters one, two, three, four, five, etc...decimal places in C9 then show the same amount of decimal places in C15 after the calculation is done and include any trailing 0's that are needed to match the number of decimals in C9.
I've tried different If statements with custom format to try to get the format of C9 transfered to C15 but haven't come across the right way to do this.
When I type a date in, e.g. 01/01/07, the result is 01/01/2007.
01/01/28 => 01/01/2028
01/01/29 => 01/01/2029
01/01/30 => 01/01/1930
01/01/31 => 01/01/1931
Excel resets itself to '1900's' after XX/XX/30. Is there anyway to change this so any year I type will always be '20XX'?
How to be able to convert decimal hours into time format.
Here are the examples:
12.2 = 12:02:00
98.41 = 98:41:00
300.42.1 = 300:42:10
and so on and so forth...
Is there a way to format a number into 2 decimal places and when you select the cell you wont see the true value?
For example:
316.2696
I still see the 316.2696 when I click the cell. Instead of 316.27 only.
If I enter 2.5 into a cell, and then change the format so that there are no digits after the decimal, the number changes to a 3.
2.4 correctly rounds to 2, but shouldn't 2.5 round to 2???
I thought that even numbers were supposed to round down, and odd numbers round up when there is a 5 right after the decimal.
I am using Excel 2003. I am attempting to use the Accounting format with numbers that should not have any decimal places (although what is entered might have a decimal place). The numbers line up fine on the right, however, the dollar signs on the left are not lining up. It looks something like:
View 4 Replies View RelatedI trying to create a schedule on my 2002 excel. I placed all the times in HH:MM AM/PM format. But somehow...I don't know what I was doing...all the times instantly appear as decimals on the cells. The times I inputted still appears in the cell's formula bar. I tried changing the cell's format to my prefferred time format, but the cells wouldn't budge.
View 4 Replies View RelatedI have a problem with the currency format with 4 decimal places.
Sub go()
Range("C5") = Range("C5") + Range("C4")
End Sub
as you can see it adds the conecnt of "R4" to what is in "R5"
If works fine when the calls are formatted as number with 4 decimal places.
example if R4 is 0.1111 and R5 is 0.2222 after running the macro R5 becomes 0.3333
But if the cells are formatted as currency and 4 decimal places the then excel returns $0.3300
I need to never have a trailing zero be displayed but still have decimals displayed (we can use up to 3 digits for the example) Here's a list of test cases
Value______Display
1.234567___1.235
1.234______1.234
1.23_______1.23
1.230______1.23
1.2________1.2
1.200______1.2
1.0________1
1.000______1
1.020______1.02
1.0002_____1
0__________0
0.001______0.001
0.0001_____0
The only time I want to see a 0 is when the answer is 0 (or rounded to 0) or when 0 is required in the middle. This is for patient safety if you are curious. 2.0 when printed out or written by hand can be mistakenly read as "20" in the heat of things, thus 2.0 should be written/printed as "2" (no decimal on the end) not as "2." even, just "2"
Is there a way to format a cell based upon a condition? If the cell value is <1, I want to show two decimal places. If the cell is >1, I want to show zero decimal places. I tried to use the conditional formatting, but there is no option for this.
View 3 Replies View RelatedI'm writing a macro that will automatically change the display in a column of cells.
The input in the cell would be a decimal value, (e.g. 1, 1.25, 1.5, 1.75, 2). As of now, I think the only input options are whole numbers and 1/4, 1/2, 3/4 fractions.
After inputting the decimal value, the cell should update to display the value as the following string format :
Input: 1.25
Displays: 1-1/4"
The purpose being to enable fast data entry while displaying in the desired format.
Here is where I know to begin for the automatic update:
Code:
Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'
'
'Code
'
'
Application.EnableEvents = True
End Sub
I have the following code
Code:
Private Sub ComboBox1_Change()
TextBox1.Text = Range("P" & UserForm1.ComboBox1.ListIndex + 3).Value
TextBox2.Text = Range("N" & UserForm1.ComboBox1.ListIndex + 3).Value[code].....
In TextBox3, the user will input a percentage as a whole number, IE: 5 for 5%, but when the update is made, it is writing the "5" as 500% and is writing 50 as 5000%.
If there is already a percent in the cell it calls up IE: 50%, it shows up in TextBox3 as .5%
I need to be able to convert big hex numbers into decimal numbers. The problem that I have with this is that when you put a hex number that includes letters excel considers it text. And if the number is too big, you can't convert it to decimal format, and when you try to, it comes up with the #NUM! error.
Number: "78652385098375A879FF747C9847A00" without quotes
I have employee experience data in decimal format in different columns like 4 years 6 months = 4.6
Is there a way to Sum the Values of cells so that it gives total experience in decimal format as same??
A1
B1
TOTAL EXPERIENCE (EXPECTED TOTAL SUM)
4.6
4.6
9.0
5.5
5.5
10.10
2.11
2.11
5.10
Is there anyway to be able to bring decimal point forward without affecting the numbers using any formatting, which will still be there even after refreshing the query.
For example, 0.0531 to 53.1
Is there any way to change the decimal point from the usual period symbol (.) to a comma (,). The reason being, i have a structural design spreadsheet and the new terminology is just so, changed from 00.00 to 00,00 I have tried cell formatting but it just uses a comma (,) for numbers in their thousand and it wont do what i need.
View 3 Replies View RelatedI am having problems applying custom format to numbers that need to be formatted.
custom format use is 0",00". When I apply the custom format above to whole numbers, I achieve the results I want:
0.00
20.00
40.00
becomes
0,00
20,00
40,00
However, when I apply to numbers with decimal places
0.20.......
how I can change the custom format so that the period is replaced with a comma but the decimal values are preserved?
I would like to do the following: * Format numeric cells (cells with a percent sign) to zero decimal places. I have a set of macros that I am combining to clean up a data set and these are two of many (removing blank lines, duplicates, blue/bold, etc.)
View 2 Replies View RelatedHow can I have a form field automatically fill some aspects of the data that's input? I've seen it before, like a phone# field automatically including the dashes.
I would like to automatically include colons in a time field. So if I were to type "081500" (or even "81500"), it would complete it as "08:15:00".
I am looking to create a spreadheet that will automatically shade blocks in a gannt chart according to a list of dates.
I.e B13 & B14 will have the start and end date of the particular task or activity (this will be on another sheet)
Row A3 - S3 will contain dates, iusing a lookup formulas excel will look up the activity name (or number) and look for the relevant date, if the date listed in row 3 is within or equal to that date it will return yes, if not a no.
this seemed straightforad, but I cant think of a consistent formula that will do this, without having to keep changing it ...
How to convert decimal numbers which represent minutes into time format? Like this
Sheet3 GH1MinutesTime212.9729729700:13:00325.9459459500:26:004116.756756801:57:00
I 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 have a list of cells that has two values concatenated. The first I would like to round to 2 decimal places, and the second is always a integer
Consider this formula..
Code:
=IF(SUMIFS('Analysis Data'!$I$5:$I$1840,'Analysis Data'!$C$5:$C$1840,3,'Analysis Data'!$E$5:$E$1840,1)=0,0,SUMIFS('Analysis Data'!$I$5:$I$1840,'Analysis Data'!$C$5:$C$1840,3,'Analysis Data'!$E$5:$E$1840,1)/COUNTIFS('Analysis Data'!$C$5:$C$1840,3,'Analysis Data'!$E$5:$E$1840,1,'Analysis Data'!$I$5:$I$1840,">0"))
&"("&COUNTIFS('Analysis Data'!$C$5:$C$1840,3,'Analysis Data'!$E$5:$E$1840,1,'Analysis Data'!$I$5:$I$1840,">0")&")"
In one cell this yields the value;
2.42553191489362(47)
In another it yields 2.975(40)
and so on..
I would like to format it to display as 2.43(47) and 2.98(40)
FWIW this means average of 2.43 games refereed per session in 47 sessions of refereeing.
I use datediff to calculate the difference between 2 dates in Months.
Result = Datediff("M", Date1,Date2)
I need the result to be formatted to 2 decimal places ie 23.52 (months)
Not neccessary to use datediff if this doesnt work.
Please note I have cross posted this HERE because I couldnt get connected to ozgrid for some reason, I will montior both sites for a solution and post back results. Apologies for the inconvenience.
I would like to format a Label, such that it displays numbers with only 2 or 3 figures after the dot. Apparently it's not obvious for Labels.
View 2 Replies View RelatedI'm trying to use a Monthview Control to put a date in a textbox and need the format to be in 'dd mmmm yyyy'.
The problem is that whilst I can get the date in that format, it is US format - so it is putting today in as '05 March 2013'.