Function Timings(StartTime As Double, EndTime As Double, Times As Range, Calc As Long)
StartTime will refer to a cell in Column A that holds a date and time EndTime will refer to a cell in Column B that holds a date and time Times is the range of cells that hold times of the day I am testing against. The cells are within a range defined called Hours. Calc refers to different criteria that I am testing again. This is a number list between 1 to 5.
The function works well if the cell that StartTime or EndTime refer to cell values that are date/time. If the data is anything different, I get a #VALUE error such as if the cell contents is text. I tried to trap the error to see if the data inputted in the cell is not date/time and thus cannot be converted to a Double but it always throughs up #VALUE and the function seems as though it fails when it . Is there anyway to trap the error of wrong data type in the first the first few lines of code in the function?
Example
Column A Column B Column C Formula
05/01/2013 12:00 06/01/2013 09:00 =Timings(A1,B1,Hours, 1)
25/02/2013 15:00 30/02/2013 14:30 =Timings(A2,B2,Hours, 1)
03/01/2013 16:00 abc =Timings(A3,B3,Hours, 1)
Row 2 will return #VALUE becuase 30/02/2013 14:30 is an invalid date (30th Feb does not exist so Excel seems to pickup as text)
Row 3 will return #VALUE because it is text and cannot be converted to a double data type
I'm trying to figure out an Internal Rate of Return for a spreadsheet. The answer is supposed to be: 29.42% however I'm getting 25.94%. Does anyone know what I'm doing wrong in my IRR function on the Profitability worksheet? I'm currently using Excel 2007 by the way.
I was trying to explain modulus to someone and they wanted to know why you can "flip" symbols mod(-6,7) = 1 in Excel. So I got to explaining that -6 Mod 7 is the same as -6-(|-6/7|)*7 which is how you get 1.
And that's when I realized... |-6/7| = 0 not -1. Then I looked in VBA and sure enough -6 mod 7 = -6. Apparently the problem boils down to the Integer conversion. Excel is performing the integer coversion by rounding down (INT) wheras VBA appears to be using CINT.
So here is how it work out in excel: -6-(|-6/7|)*7 -6-(|-0.857142857|)*7 -6-(-1)*7 -6--7 -6+7=1
But in VBA you get -6-(|-6/7|)*7 -6-(|-0.857142857|)*7 -6-(0)*7 -6-0=-6
I have the following equation programed in Excel, (see file attached). I do not understand while the match funstion is not working the lookup value is the on in cell N1 the array is from B1:F1. The return value should be 3 but I get #NA instead at the begining I was thinking that the values were different but then I compared cell =D1=N1 and the result was TRUE, meaning that they are equal
The attached workbook contains a cell with validation list selection and a look up function. The validation list selection works fine, but the look up function is coming up with the wrong results on several items. Is this a bug or am I doing something wrong?
when you type True into a General formatted Excel cell Excel returns TRUE. This value cannot be formatted to a 1 (or a -1) by applying a different format. Thus, does Excel have a boolean data type? Or is it something else like a numeric value that for whatever reason is not receptive to format changes.
Wish to reorder row 6 onwards according to the following hierarchy:
1st order: year number ascending (ie the last 2 digits of the 6 digit mid: 14, 15 in our example) 2nd order: month number ascending (ie the first 2 digits of the 6 digit mid: 08, 10 in our example) 3rd order: strike value ascending (ie the number on far right: 45, 45.01 in our example)
Leith Ross kindly helped me with the below macro but for some odd reason, Excel simply does'nt like it and gives me the error message: "Compile Error: Object Required".
I need to split in Excel some thousands of data rows containing text and numbers information of different character length Data to be extracted ( parsed) is stored in a single column( the first one) and needed results look like this:
Data to be extracted Needed results Address Name,Surname Phone no. Green Street no 16 Smart town Schmit Anders +3900098787867
[Code] .......
Data is composed by addresses, name of persons and phone numbers. Person's name is has two or three parts, address and phone numbers length is different ( 9 to 12 characters) I need to complete this difficult task (for me, obviously, ) in Excel using a combination of functions and/or multiple operations.
I am trying to find a way of replacing a whole amount of data, over 1000 items, initially each cell contains this >>> 056001 Not Set, I am trying to remove 'Not Set', but the big problem I am facing is that Excel also removes the leading 0 or zeroes, when it finishes removing ... I tried several things, like changing the column to Text, and then paste the data from another column, because I read the Text format is preserved, nothing, as soon as the column is pasted it changes back to General, if I change it back to Text again, and then apply a Search and Replace, each cell is changed to 56001, which is not what I intended, the only way I have seen it works is if I manually remove the string, then it works and is changed into the text format and number as text, as I intended to
I have attached a file called Test2 which has a public function called DBLookup. The function looks in the attached access database simulating a Vlookup just like in Excel. The problem I am having is when that data appears on sheet1. I am getting #VALUE in the cell and the erroe states "a used value in the formula is of the wrong data type".
my
Dim adoCN As ADODB.Connection Dim strSQL As String
Const DatabasePath As String = "C:Test2.mdb"
'Function argument descriptions 'LookupFieldName - the field you wish to search 'LookupValue - the value in LookupFieldName you're searching for 'ReturnField - the matching field containing the value you wish to return...
I am using a For Each Next to cycle through a list of names, check certain conditions and then will be adding a further lookup (instead of Result = Result+1), but all I get is #Value!
Function maxbarometer(Name As String, Round As Integer, NameList As Variant, RoundRange As Range, RoundRangeTwo As Range) Dim Roundname As String Dim Result As Double Dim NameColRound As Integer Dim ListObject As Variant
Roundname = "Round " & Round NameColRound = Application.WorksheetFunction.Match(Name, RoundRange, 0) For Each ListObject In NameList If ListObject.Value = Name Then Result = Result ElseIf Application.WorksheetFunction.VLookup(ListObject.Value, RoundRange2, NameColRound, False) = Roundname Then Result = Result + 1 End If Next maxbarometer = Result End Function
when i try to get the final row witht the following:
KROGrow = Cells(65536, 1).End(xlUp).Row
i am getting a row that is consistently 27 rows off of when i manually do control up? i am pasting data from and existing workbook into a newly created workbook in the sub. i would assume that since the destination is new that they would never be any data in this?
I am trying to use the Vlookup function to return State name based on the corresponding state code.
I have the list of State along with the Codes in adjacent sheet, arranged in ascending order. I am using the Vlookup function to list the State name based on the State code.
Vlookup function: Vlookup(A4,Sheet2.A2:B51,1)
This function returns me a wrong value. It gives "Arkansas" for the state code "AZ", when it should be Arkansas.
Also, if I add "FALSE" as a 4th argument, it returns "#N/A".
I have the following formula: IF(SELL_INVESTMENT = "YES" , 1,2) It returns not 1 or 2 but 0. The worksheet contains over 1000 cells. Trace Precedents verifies that the formula evaluates the correct cell and that it contains "yes". I created a very simple worksheet to test the formula and here it correctly returns 1.
When copying data from a website to excel, excel recognizes the dates wrongly.
About half of the dates he sees as: DD/MM/YYYY, while it should be: nothing/MM/DD.
For example: The cell contains the date: 01/04/2029 (The first of April, 2029)
While it should actually be: 04/29 (The 29th of April, no year!)
For all dates it starts with the 1, followed by a month, followed by a year (which consists of the year 2000 + the actual day), which is totally wrong.
About the other half of the cells, it just contains text with the month and the day (which are correct), but excel doesn't recognize this as a day.
For example: The cell contains the text: June 12
When I set the cell format to date (or any other format for that matter), nothing happens, so excel does not see this as a date.
I have a problem with my references when sorting in excel. I have been searching for the problem, but did not found a useful solution. I'm using excel 2003.
Let me try to explain my problem with a simple exampel.
I have a column with numbers and one with formulas pointing at these. A B 2 =A1 4 =A2 3 =A3
Now I want to sort column A, and the column B should keep pointing at the original number. So I want this: A B 2 =A1 3 =A3 4 =A2
But I get this: A B 2 =A1 3 =A2 4 =A3
This output is what I would expect if I used absolut reference($).
Using offset(B1;0;-1) will work, but I cannot use this, since I will add/delete rows and the reference is not neccessarily next to it. Plus it can be a rather large sheet.
I cannot use dynamically name definition either, because it is intended for other users, and this will be too much work for them. What I basically need is a simple formula they can enter.
My sorting is done via a VBA macro, so if there is anything I can do through macro/VBA that will be okay as well, since I can do this without the user seeing it.
I have this workbook with 10 sheets. Each sheet has macros that are called from a floating toolbar. What I am trying to do:
I want to create a msgbox that warns me when I click in a toolbar's button to call a macro that not runs in the activesheet. Something like: "This code (or macro) doesn't match (or run) with the active sheet.
as such Excel (2003) is putting in the wrong weeknumbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem?
I am using the following formula, could someone explain this formula in english?
Thers no easy way to explain this other than looking at the sheet (which is attached), but basically i have a load time start time and end time. In the example in the sheet the load time is 01:00 The start time is 23:00 The end time is 23:35
Th eload took 35 mins and was not late as it is going 01:00 next morning, yet the answer it replies is 'N' which makes it show as late. Does anyone have a formula to slove this issue
The code is supposed to project review dates 1 month, 2 months and 3 months after the start date, but the first "cell offset" line on is projecting the next day instead of next month, its driving me nuts!!, the others are working fine
Code: Sub reviews() Dim cell As Range
For Each cell In Range("L1:L200") If IsDate(cell) Then cell.Offset(, 10).Value = DateAdd("m", 1, cell.Value) cell.Offset(, 11).Value = DateAdd("m", 2, cell.Value) cell.Offset(, 12).Value = DateAdd("m", 3, cell.Value) cell.Resize(, 10).AutoFill cell.Resize(, 11) End If Next cell End Sub
I have a very basic spreadsheet to calculate golfer handicaps based upon a course index. For some reason, my "LOOKUP" formula is not retrieving the data from the cell (column) next to the value I am searching...?
I'm trying to parse a text file into Excel and the date is coming out as "1940" instead of "2040". In the text file the date reads "5/14/40". I've already changed my regional setting in Excel so that if I type in "5/14/40" it is understood to be "5/14/2040". For some reason this doesn't work when I parse the data though.
The formula below was written a couple of years ago and I just discovered an error. It returns an "S" for the value 0789 when it should be a "C", (Consecutive).
******** ******************** ************************************************************************>Microsoft Excel - FL MID PLAY 4 CONSECUTIVES.xlsm___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutT215=OPQRST215078907893SSheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The macro was orininally set up for the 7 days of the week. Now I want to set it up for the 31 days of a month. It's set to pull information from a register sales journal. Here is the button macro-
Sub Button2_Click() Dim sDay As String Dim sShift As String
sDay = InputBox("Which Day? (1 For MONDAY, 2 For TUESDAY, ... 7 For SUNDAY)") Select Case sDay Case "1" sDay = "1" Case "2" sDay = "2" Case "3" sDay = "3" Case "4" ........................
I have this formula that returns a grade mark for a specific score in a test:
=VLOOKUP(D4,'Grade scale'!$A$34:$B$42,2)
using these values in the lookup table:
Low scoreGrade 0.0% U 25.0% G 35.0% F 45.3% E 55.3% D 65.5% C 72.0% B 78.0% A 85.0% A*
The problem comes where the Grade is A or A* as the Vlookup table can't differentiate the two. Anyone know how to modify the formula so that A and A* are seen as distinct values?
