Would like to do the following for our school,
In cell A1 there is a number, for instance: 12.12345
Based on that number, the 3rd number after the decimal is important.
If the 3rd number after the decimal is “x” then the contents returned in b2 is a text value ( like high school ) .........
I 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 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.
is there any way to use the right function to return any data to the right of the decimal point?
eg 117.22 would be .22
108.1225 would be .1225
102.358 would be .358 etc
During finalization I have to convert the numbers into millions. But due to rounding factor, the total doesnot match with the displayed fig. how can I sum only upto two digits of the fig? A sample file is attached.
View 9 Replies View RelatedI need to add a decimal point to a column of numbers. For example, where it says 126 needs to be changed to 1.26, 3035 changed to 30.35, 13593 to 135.93 and so on. Can this be done automatically or with a formula?
View 2 Replies View RelatedThis problem just appeared recently, as if out of the blue, and it seems to affect all of my old work sheets as well as new ones. I am using 2002 version. When I input any number in any cell the program automatically inserts a decimal point. Thus 1 becomes .1, 12 becomes 1.2, 123 becomes 12.3 etc. I have tried formatting the cells to "general" and to to "number" with no change. The thing that really gets me is that old saved worksheets exhibit this behavior as well. I can work around the problem by inputting 1. or 12. which then gives me the entry I am after but I've never needed to do this before.
View 2 Replies View Relateda1 -> 2.333.33 "i want this to return as 2,333.33"
a2 -> 33.33 "i want this to retun 33.33"
a3 -> 45.555.55 "i want this to return 45,555.55"
i already asked this question and someone gave me this code -> substitute(a1,".","",1) which return to 2333.33 but the problem is for the a2 if i use the code it will return as "3333" which read as "3,333".
Is there any way that we can determine if the values have two decimal point before it will run the command?
I've just imported a file into a UK version of Excel from a German made datalogger. The decimal points (which should look like this 0.0) all appear as comas (0,0) with the result that Excel can't read them.
View 5 Replies View RelatedI need to allow only in textbox1 Numbers & decimal Point.
View 4 Replies View RelatedI have one problem in my data
In the amount Column, the amount looks like
10000, 10025, 20025 and so on
The amount should be
100.00, 100.25, 200.25
The decimal point is missing,
Any formula for the decimal point to appear in every last two digits
I am looking for a formula that will calculate the number after the decimal point, for example: A1=0.4656162 I want to multiple the number after the decimal point by A2=60, is there a simple formula to enable me to do this as I have to perform this across a large number of cells.
View 2 Replies View RelatedIs it possible to enter a number into a cel as: 12345 and have it display as 123.45.
View 10 Replies View RelatedI have just started a job and given a task to input some timesheets that were done manually. The problem is that I used decimal point instead of the colon. When I try and format the cells I do not get the real time.
Here is an example: ....
How to make calculation in excel without decimal point automatically. I use this formula =MIN(360,30*F42/360) . Total displayed 144. By right 142
DAY
FORMULA
RESULT
BY RIGHT
361
=F12/360*30
30.08
30
365
=F13/360*30
30.00
30
365
=F14/360*30
30.00
30
366
=F15/360*30
30.08
30
269
=F16/360*30
22.11
22
=MIN(360,30*F42/360)
144
142
So how can it be that Excel calculates Pi()/90*90=Pi(); but Pi()/90+Pi()/90+Pi()/90......90 times is 0.000000000000033527. And why is the number zero out to 14 decimal places not being returned as 0?
I need to know exactly when a series of sequentially increasing radians = Pi(). The length of the series is dynamic and can be as few as 6 and as many as 120. And right now Excel is not cooperating.
Here is what excel thinks Pi()/90+Pi()/90+....90 times =
3.14159265358980000000
3.14159265358979000000 (this is how excel displays Pi() out to 20 decimal places)
So Excel thinks the two numbers are not equal to each other, and what could be more interesting is that Excel thinks the difference is
0.00000000000000355271
Even after copying and pasting both numbers as values out to 20 decimals. Excel still thinks the difference is 0.00000000000000355271
How is that possible?
I have Excel 2013. Is there some sort of bug here? I had posted earlier about major headaches when using natural log. Random cells keep getting forced to 14, 15, sometimes 16 or 17 decimals. No rhyme no reason. I can custom format every cell in worksheet to 2 decimals, and as soon as I hit F9... ############# all over the place. At least with Pi() the decimals are under control, but the math is not right. Pi()/x + Pi()/x.....x times = Pi().
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
I have a column that has values ranging anywhere from 1 to 999,999 and I need a way in vba to take whatever value is in the specified cell and place a decimal point at the far left. Example:
change 175526.34 to .17552634
change 376.1 to .3761
change 22987.254 to .22978254
when the code is run I need the selected number to begin with the decimal point.
See, the tricky part is that the length of the number varies and I cant just divide by X to shift so many places to the left..
Is it possible to Conditionally Format using the (Custom) Data Validation Tool one or both of the following conditions:-
1. Force the user to enter the correct decimal point position for a given currency :-
The are 3 different decimal point locations location's - 0.87624 / 123.123 / 1273.00 There are always 6 numerals We have a table to lookup which specifies where the decimal point is on all currencies
CURRENCY AUDUSD< List is linked to a table
Price - High 0.87744< Decimal point dependant on chosen currency pair chosen
Price - Low 0.86596
Price - Close 0.86823
2. As above but numbers once entered will be formatted accordingly to the correct decimal place to the corresponding currency
I have 100 rows and 100 columns, where row 1 is dates, and column A is times, and within are numbers. On a second sheet I have a column (A) with random dates and another (B) with times. What I need is a third column (C)on the second sheet that looks at the date and time, finds the intersection on the first sheet, and returns the number found there.
View 14 Replies View RelatedI have a spreasheet with partnumbers listed in column A, a safety stock value in colum B, a restock date in column c, and quantities listed by month needed in the following columns, i.e.:
A B C D E F G
Part # Safety Stock Restock Jan Feb Mar Apr
123abc 50 Feb 20 35 20 10
In the example above, it is apparent that the safety stock will be delpeted in Feb.
I am trying figure out how to calculate the month that the safety stock will be depleted
and show this in another cell.
I need help in simulating the functionality of the OFFSET function with some of standard Excel functions which are shown at this webpage (I will be using this converter to generate the webpage from the sheet):
Spreadsheet Converter Supported Functions
I ask this because I need to create an interactive online calculator which absolutely needs to have the ability to allow the user to select the RANGE of the data to be analyzed. I included an example sheet with the offset function in the pink cell…Do you think any other standard excel function can be used to simulate its functionality?
I am trying to return the first occurrence of a variable text string with-in brackets for all data on the spreadsheet using the "RIGHT" excel function, the text to search is in column D.
Example for cells D2 & D3 reads:
D2:Adv Costs (27a) - would return "27a"
D3:Designer Unit (60) (Base) (50abc) - would return "50abc"
how do i get the numbers after the point.
View 9 Replies View Relatedtrunc gives just the numbers before the decimal point. how do i get the numbers after the point. original number minus trunc seems messy!
View 3 Replies View RelatedI have a pivot table as shown below which is pretty straight forward however I am trying to create a macro that will automatically change the point name to match the point name in column E then copy that resulting dispaly to another sheet then pick the next point name in line and do the same thing and repeat for 50 rows, so my end result will be 50 pivot tables ready for printing. I can do this manually but I am trying to make it automatic...BTW the point names in column E change everyday but the pivot table supports the name changes.
Option Explicit
Sub PointName()
Dim Ws As Worksheet
Dim Rng As Range, Cel As Range
Set Ws = ActiveSheet
Set Rng = Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp))
For Each Cel In Rng
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
Ws.Columns("A:B").Copy
Sheets.Add
With ActiveSheet
.Paste
.Name = Trim(Cel)
.Range("A1").Select
End With
Next
Ws.Activate
End Sub
I turned it off for a while and when I turned it back on I am getting an error
Unable to set the _Default property of the PivotItem class
Debugger is highlighting
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
I have created a userdefined function. the Idea is for the user to calculate the difference in hours and minutes. The input would be like budtime = 30 , realtime = 1.25 the answer I would like to have is 28:45 (28hours and 45 mins). Im aware that I can format the cell manually with [h]:mm but I'd like the function to do it.
I have written this:
Function RT(BudTime, RealTime)
RT = Format(((CDec(BudTime) - CDec(RealTime) / 24)), "[h]:mm")
End Function
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 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