I am trying to write a function that recalculates given times to points (as used in speedskating).
The times are filled in by a number of people, which each use their own method of dividing minutes from seconds (and 1/100 of a sec).
I found out how to see the difference between times that look like 1.11.11 and 11.11 etc
How to use that same method in an InStr function. Most of the times I get an error message #Value or something that has that format, sometimes all I get is a 0.
I want to change my existing macro using InStr function in such a way that when the columns are found then it add the corresponding values. The addition of values have already been done. I just want that if similar values are found then it show the results.
The example workbook with macro is attached : comparestrings.xls
I am attempting to use the following code to move certain sheets to specific locations in my workbook. The case statement containing the InStr function isn't working eventhough the "Data" tab does exist and the InStr function does return 1.
Code: ThisSheetToMove = Sheets(SCount).Name Select Case ThisSheetToMove Case "Schedule" Sheets("Schedule").Move Before:=Sheets(1) Case InStr(1, Trim(ThisSheetToMove), "Data") > 0 Sheets(ThisSheetToMove).Move After:=Sheets(SShtLast) End Select
I want a UDF; = OK (f1,f2) each argument being an alternative function. If f1 returns an error message then use f2. Should be easy but I cannot get it to work.
In column D I have the time of the end of my shift. When I try to input =MIN(1;$D:$D) in column E, i get the same answer (0,625) for any row, while =$D:$D gives correct values. So I guess it has something to do with the MIN function
Have a spreadsheet prepared by someone else on older Excel version exported from database. The sum and count functions work fine both vertically and horizontally, but trying to add selected cells returns #VALUE! Attached is small range of 50-col x 2500 row spreadsheet. Original content is highlighted in yellow. See cell E2. I assume the forumula is seeing number content as text. How do I convert the entire spreadsheet to numbers.
I am working on a Monthly Vehicle Spread Sheet. One of the outputs I am trying to achieve is an automatic calculation of Mile Per Gallon. To do this, I need to know if there is a function that will return the value of the first entry of a group of cells.
In calculating the miles per gallon, I need to subtract the first gallon amount entry of the total gallons in the month, then divide that number into the difference of the mileage in the month recorded when the vehicles fueled up.
I'm a newbie who has a simple "Area()" function that multiplies two numbers. I copied it off the Net as a programming test. I added to a Module of my Workbook using Tools>Macro>Visual Basic Editor. I put it in "Modules>Module1". Problem is, it doesn't seem to be recognized in my Workbook. When I enter "=Area(A1,A2)" into a cell (without the quotes) I get "#NAME?". What am I doing wrong? Here's the function:
I'm using the following phrase out of a much longer expression to demonstrate that the return value of the MID function seems to be nothing I can understand.
This expression:
=MID("6/10x",1,FIND("x","6/10x)-1))
returns 6/10 as text which makes sense. However I was under the impression that when text in Excel contains numeric characters, it can be interpreted as a number. So then how does the following expression,
=MID("6/10x",1,FIND("x","6/10x)-1)*1
return the result 38878? Does it still think it's text?
Im trying to do a lookup but when I drag the formula down it returns the wrong values and also returns #N/A or #REF (which I think means it's refering to a wrong range and cant find the data???). How can I do a look up function by dragging the formula down a long column of data eg. 65,000 rows in excel, that returns the correct text. I have attached a simple example- see attached.
I am trying to get the week from a given data (from a cell that has been correctly formatted with date). =YEAR works, =MONTH works, =WEEKNUM does not work. I am using Excel 2003 and I have tried in both my workplace and at home...
I.e., I have a date in C2 (18-05-08), and =YEAR(C2) returns 2008, =MONTH(C2) returns 5, while =WEEKNUM(C2) returns #NAME, and =WEEKNUM(C2,2) does not work at all...
Let's say I have data that looks like this grid below. Each color represents a column heading and each number is a data point within that column.
Blue Red Green Yellow 18 27 15 36 56 41 3 22
Can I write a formula that would do the following 2 things with the data in this format:
1. Find the max of the data...simple =Max(...) formula 2. Use the max to return the column heading. In this case the max is 56 so the value I want to return is "Blue"
I've never quite mastered the idea of passing arguments between subs and functions. I have a project right now where I am writing this same bit of code over and over, for different parts of the macro:
I have calculated the t values then i have three tables for 3 confidence levels, 0.01, 0.05,0.1 in which I compare the t value with the value from the t-test table. If the t value is larger it returns a 1, if not a 0. This works fine.
To summarise the information I have a final table which I wish to return the highest significance of the given t value.
I am using this =IF((G53=1),0.01,(IF(G44=1,0.05,(IF(G35=10,0.05,"none")))))
the problem I have is it always returns 0.01 even if g53=0. If i remove the formula from g53 and replace it with a 0, it returns 0.05 instead (even if g44 is retruning a 0).
I have considered copy paste values and doing this but that rather removes the automation aspect of the spreadsheet if I need to make adjustments.
I have some survey data for 2008 & 2009. I have a column calculating the difference on each question between the two years. I need to show the 20 biggest changes, positive or negative. Which function can I use for this?
I've been asked to adjust some thing with a worksheet someone else created. On the sheet labeled "Criteria" there is a list of dates and some formulas that use these dates. I can't figure out why the result of the formula is a number, i.e "0"; I thought it is supposed to return "TRUE" or "FALSE". If I insert a new sheet, within the same workbook, and insert the same formula, I get "TRUE" or "FALSE". For some reason, the "Criteria" worksheet doesn't return "TRUE" or "FALSE", it returns numbers. I just need to know why this is happening so I can replicate it in the new workbook I'm creating.
In the attached file I used =MATCH(TRUE,INDEX($B$2:$B$10="",0)) to retrieve the location of the first empty cell.
When using the "Evaluate Formula" tool, it is clear that the position in the array created by the INDEX function is the 6th. Nevertheless, the final outcome is 9, being the last cell in range.
Is it possible to see why this code returns a date that is almost correct? Searching for 01.01.2005 I get 01.11.2005 as result. I would like the code to only show the correct date, and if it doesn't exist it should end up showing "nothing".
I am trying to write a UDF that will calculate a value and return it to the cell, and also create a comment in the cell showing the formula used. Here is an example of the "=calculate(A2,B2)" placed in cell A1
Function calculate(ByRef X As Range,ByRef Y As Range) calculate=x*y calculate.addcomment "calculate = " & X & " * " & Y End Function
the problem commes from the add comment field. I can add a comment to any cell but the cell the formula is called from.
I setup this function to pull my staff's schedule an auto generate the forms i need however the lookup function works properly sometimes and improperly others i checked the syntax and it is correct how ever the wrong data is continually returned. I have attached the file if anyone can help. the VISUAL PLAN tab is the one contaning the formula and it pulls data from PLAN, CURENT SCHEDULE, AND CATAGORY SUMMARY however the only formulas hat i have a problem with are the ones referencing CURENT SCHEDULE.
I'm compiling a list of names on a separate tab from a column that only lists the names sporadically. Problem: In the formula, the IF provides a TRUE, the row function, and consequently, the INDEX function (when I hit F9) provides the correct "name" and row number, but when I hit return, it provides the text 4 cells beneath what it should.
When i have US or ULS or ULT alone in the column the macro is working perfect But when i have US,ULS together my macro is pulling the information from US.... and is not using the comand .......ElseIf ((InStr(series, "US") 0) And (InStr(series, "ULS") 0)) Then
If (InStr(series, "US") 0) Then Call HOME div = "2" cntr = "Sold"
ElseIf (InStr(series, "ULS") 0) Then Call Univer div = "12" cntr = "House"
ElseIf (InStr(series, "ULT") 0) Then Call masters div = "Pi" cntr = "Down"
ElseIf ((InStr(series, "US") 0) And (InStr(series, "ULS") 0)) Then Call Univer div = "24" cntr = "twin"
