[URL]
I tried the following formula on excel 2010:
=ROUND(2.01,2)-ROUND(2,2)-ROUND(0.01,2) but still I am getting -2.13371E-16 as a result.
How can I make it return 0 instead? I am trying to compare the above formula with 0: =2.01-2-.01<0. it returns TRUE, which should be FALSE.
I'm in Excel 2010, and the cell with the date I want to work from is G22e?
EDIT: I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days. (I also meant H22 not G22)
Here's the latest thing I tried (that doesn't work):
=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))
EDIT #2: I guess I should probably note that the date in H22 is the result of another formula
=EDATE(G22,12)
Using Excel 2010
Workbook with 21 sheets, first sheet is the summary sheet (named SUMMARY) taking all it's content from the other sheets, named from 1 to 20, which are all laid out the same, but contain different data.
My problem is that I want to get rid of decimal places that are being brought into the SUMMARY sheet
In sheets named 1 to 20, I am using the following formula in H23 to arrive at a figure =IFERROR(100/(B5/B23),0) in H23
B5 and B23 are always whole numbers on all sheets 1-20, result can be whole or contain decimal places which I round up in the cell to show no decimal places
On sheet 1, B5 is 200 and B23 is 5, giving 2.50 as the answer, which is right, I have H23 set as a number cell with no decimal places so it rounds up to 3 which is what appears in the H23 cell.
All of the above is correct and appearing as I want it on the sheet, the problem arises when I import into the SUMMARY sheet as follows:
On the SUMMARY sheet in cell B59 I want the following to appear 5 - 3 but am getting 5 - 2.5 using the following formula
='1'!B23&" - "&'1'!H23
The first part is fine as B23 will always be a whole number
How can I stop it from bringing in the 2.5 and get it to bring in the rounded up 3 into cell B59
I have cell B59 set as a number cell with no decimal places but it still appears as 2.5
Also when I correct it as it appears a lot on the summary sheet, how do I get the following to happen
='1'!$B$23&" - "&'1'!$H$23
if I fill the above across columns how to I get the two '1' s in the formula to become '2' then '3' as I fill across.
Basic Excel-2010 functions.
I have given a table with some decimal values like e.g 0.3658 0.358 0.485 0.7863 0.558 0.858 0.985, I want to find the sum and average of these number.
1) I have tried the autosum or auto Function =AVERAGE(A2:A7) but it yields #DIV/0! error
2) when I use =AVERAGEA(A2:A7), it yield ans=0
3) puttin =ROUND(AVERAGEA(H2:H23);3) also yields 0;
Need to find out exact date. if I insert 32.5 (32 years 5 months ) in particular cell. my output shld be 10 june 1974 or 10/06/1974. I want to find back date...
View 4 Replies View RelatedI 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 problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
[URL]
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 RelatedI am trying to send bulk emails from my excel 2010 - however I am getting a POP UP. find the screen shot in the enclosed word document So every time a new mail is sent from excel we need to press the button allow Is there a way where I can turn off this warning.
View 6 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 am trying to subtract an amount from a cell until it reaches 0, then move and subtract from the next amount, and so on.
AmountFixed BudgetedResult53.50Subtracted 5 tell it hit zero1.30Subtracted remaining amount until zero43.8Subtracted remaining amount until zero3.8 was left over without hitting zero is good. Is there formula for this?
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 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.
believe this is a simple formula I just cant seem to get it right. Example: H1 has 859.375 and I need H2 to be 71' 7 3/8
View 5 Replies View RelatedSo 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().
I am using Excel 2007 and importing a list of coordinates. The coordinates are in a text file and separated by commas, so I'm opening the file as a delimitted file. All of the coordinates import correctly except one. The value is supposed to 52530.6372, but Excel is automatically changing it to 52530.6371999999
If I manually type a "2" to replace the "1999999", it changes it back. If I type "3" it stays as a 3.
I added a round function to force it to round to 4 decimal places and that seemed to work until I then tried using the number with text commands. When I used an ampersand to insert the number into a text string, the additional decimal places are back, even with a round function!
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")
Is there a way I can get excel (2007) to limit itself to 2 decimal places for all numbers entered? So effectively I don't want use the formulas TRUNC or ROUND (or any other formula) but I would like rather excel to behave like my calculator where I can limit the decimals to 2 no matter how many I input.
In other words, every time a input a number, excel will round it up automatically to 2 decimal places and the number available will have 2 decimals only. Is there any option hidden somewhere in the Excel options that I couldn't find?
I have listed a total amount of timings for a study that I have totalled giving me a figure of 34.76 which is under an hour.
how do i convert to min and seconds - is there a formula?
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 RelatedAny 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 have a code that inserts a new column after every 7th column. I want to include a formula where every 7th column value is subtracted from the values present in the column before the 1st, or you can think of it as subtracting 7th column of the present group from the 7th column of the previous group. Example: The range of my data starts from col F, then
F (7th) New Column (G) H (1st) I (2nd) J (3rd) K (4th) L (5th) M (6th) N (7th) New Column (O) P (1st) Q (2nd) R (3rd)
So, New Column (O) = N - F
and the next New Column (W) = V-N ...
NOTE: Column G can be ignored.
I want to add a looping function to this so that it will continue to subtract for the other respective columns as well. How do I incorporate this into the following code?
[Code].....
I'm using Excel 2013.
I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
Sub aaa()
'
' aaa Macro
'
[Code].....
Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).
I am using excel for windows 2010. The macro that i have so far is below.
Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value
[Code] ........
I am trying to adjust the below macro so that it will work in Excel 2010.
Sub OpenAndProcess()
Dim fs As FileSearch
Dim I As Integer
[Code]....
I 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?
keep getting "The formula you typed contains an error".
To calculate a person (or anything else) age you basically subtract his date of birth from today's date. However which date functions to use depends on the amount of precision and the output format you want.
3. If we want his age in years and months, then we can use a formula like this one:
=INT((TODAY()-A1)/365.25) & ” years and ” & INT(MOD((TODAY()-A1)/365.25;1)*12) & ” months”
The output of this formula will be as follows:
40 years and 2 months.
I change A1 to G3 because that is where I the birth date.
I'm having problem importing a CSv file into Excel. The CSV file is around 4-5k lines.
The layout is similar to this :
H1, H2, H3, H4, H5, H6
A4, A3, dummy, Dummy, ~,
A4, A3, dummy, "Dummy ,Dummy", ,
A4, A3, dummy, Dummy, +,
A4, A3, dummy, Dummy, -,
A4, A3, dummy, Dummy, "(5 + 4) /4",
Consider that the formula (5 + 4) /4 has a CR and LF between (5+4) and /4
I've tried a few methods to import the files:
Reading the CSV file as a Text File a line at a time but with the CR a LF it get's all messed up
[Code] .....
Tried with Using OLEDB but the Sign + , - and ~ are read a Null
[Code] ....
And Finally tried wit th QueryTable but it does not recognize the Double Quote that precedes the comma so
"Dummy ,Dummy" it's seen as two column values instead of one.
[Code] .....
I am trying to use the code that can find the last used row in particular column.
Here is the code in module1
Code:
Function LastRow(rng As Range)
Dim temp, temp1
Dim col As Range
With Application.Caller.Parent
For Each col In rng.Columns
temp = Cells(Rows.Count, rng.Column).End(xlUp).Row
If temp > temp1 Then temp1 = temp
Next col
End With
LastRow = temp1
End Function
and code in the range
Code:
=LastRow(V12:V300)
My problem is, this code was working nicely but now it only shows ###.
PS I use MS office 2010