Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?
Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double
[Code]....
Is it possible to replace double returns with single?
View 9 Replies View RelatedI am working with a fiscal year that starts in December and ends in November. I want to make a user defined function that will return the fiscal year of a date. I've created the below code, but it returns a zero.
Function FiscalYear(DateFY)
If Month(DateFY) = 12 Then
Year (DateFY) + 1
Else
Year (DateFY)
End If
End Function
I am trying to write a function that returns a single dimension array from inputed data. I want this to work for any data, i.e. a single cell, an array in vba or a range. I am using the "for each" staement. However, when I pass an array in the "for each" returns the same range, rather than the elements of the range. I've played around a bit, and the "for each" does what I want on an range if I am not passing the range to a function. Is it because I am passing the range into a function as a variant? Here is the code for the function.
Public Function CreateSingleDimensionArray(ByVal dataToConvert) As Variant
Dim vHolder As Variant
Dim vArray As Variant
Dim lElementCount As Long
lElementCount = 0
For Each vHolder In dataToConvert
lElementCount = lElementCount + 1
Redim vArray(1 To lElementCount)
vArray(lElementCount) = vHolder
Next vHolder
CreateSingleDimensionArray = vArray
End Function
I've got an If statement in Excel which features several VLookups - I need the IF statement to return differing results to 2 different cells, is this possible?
In plain English, I'm looking for something like
If(Vlookup(A2, B2:D5, 4, False), C6="y" And D7="ok", "False")
Obviously this is a very crude example
when you type in a builtin function in the formula bar such as =DAY(), a small callout box appears below telling you the syntax - in this case "DAY(serial_number)". Is it possible to achieve this for a user defined function?
something that looks like =CELL.MIN(reference)
View 3 Replies View RelatedI 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.
View 5 Replies View RelatedI am trying to return a TRUE or FALSE based on a date in a cell.
for example:
if cell A1 = 07/11/2009
I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.
I have tried stuff like:
=IF(A1=TODAY()-14,"True","False")
but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
If a INDEX,MATCH function returns a zero, how do I get it to use the value in the adjacent cell which is a unique number?
=INDEX(Sheet2!A:C,MATCH(A2,Sheet2!A:A,0),3)
Example:
Is there a function that returns the position (numberical order) of a cell within an array?
View 9 Replies View RelatedIn a workbook I have a tab with multiple columns containing names, activity codes, billed hours, etc. On another tab I have created a budget for which I use the Excel SUMIFS function to check the database in the first tab for relevant entries and add them together in the budget sheet.
Now here's for the tricky part. On the budget sheet the names are not mentioned, only the job titles. So these are grouped together. This means that the first tab shows only names, while the budget tab only shows the function title.
The function I have used thus far is this:
Formula: [Code] ......
The first part refers to the column containing the billed hours, the second part refers to the column containing the names (criteria range 1), the third part refers to a specific name (criteria 1), the fourth part refers to the column containing the activity codes (criteria range 2), while the fifth part refers to a specific activity code (criteria 2).
This works really well as long as the budget tab only mentions the names, yet it only aggegates at the job title level. Would it be possible to use the same SUMIFS function to search the database for, say, all the names that fall within one specific job title? Essentially that would result in this somewhat bulky function:
Formula: [Code] .....
Clearly, Excel doesn't allow this.
I have been using the CONCATENATE function to merge 2 strings together into a single cell. However the problem is, the third concatenated cell depends on the other 2 existing. The idea is I want to take 2 columns of data, and use a formula to merge them, then delete the original 2 data columns so I have a single column with the full data merged.
COL 1 (A3) = First Name
COL 2 (B3) = Last Name
COL 3 (C3) = Full Name (wanted)
So I do =CONCATENATE(A3,B3) and that puts the fullname in C3, but I want to sort of "flatten" C3 so that I have the fullname by itself and I can delete the original cells (A, and B)
I have a spreadsheet that just uses the basic "SUM" function. This morning I went to use it and the function does not work after I change a number within the field of that function. If, however, I go into the SUM function and double click, then hit enter, it does the new calculation.
View 3 Replies View RelatedIf you run the Int function on a product of two variables, it will return a different (wrong) result if the variables are both defined as single:
Sub roundingtest()
Dim a As single
Dim b As single
Dim cases As Integer
a = 18200
b = 0.01
cases = Int(a * b)
Debug.Print cases;
End Sub
this returns 181 instead of 182. If you define one of the variables as double, then it works fine. Is it just me?
Anyway, I found it to be quite useful, as i was trying to get a function to round to the first higher integer - as opposed to the first lower. so
182.1 -> 183
182.9 ->183
182 ->182
So weirdly enough, int(a*b)+1 does the trick! Of course if a or b is defined as a double, then it all goes to 182. So without any IFs, this works real nice!
I was wondering if it is possible to have a double sumif. For example asssuming you have a table with three columns:
Col 1 (far left) is called Name
Col 2 (middle) is called date
Col 3 (far right) is called values
Now assuming that column 1 contains 4 different names, each repeated for each date in column 2. In turns the dates are repeated for each names (see below example)
Name Date
AAA Jul 06
AAA Aug 06
AAA Sep 06
BBB Jul 06
BBB Aug 06
BBB Sep 06
CCC Jul 06
CCC Aug 06
CCC Sep 06
Column 3 contains random values.
How can I sumif column 1 at the same time as column 2 (i.e. sumif for criteria AAA and Aug 06). Or if not possible is it possible to write a double Vlookup?
how to use the indirect function to pull data from a pivot table. This is the formula I am trying to recreate: =GETPIVOTDATA("sum of BOE",$A$14,"CLASS","PROVED","Years",2013)
I can't seem to get the indirect function to work properly with the words in double quotes, such as "PROVED". How do I format that part of the formula properly?
This is as far as I can get. =GETPIVOTDATA("sum of BOE",INDIRECT($M13),"CLASS","PROVED","Years",$N13) $M13 refers to $A$14 and $N13 refers to 2013
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
View 10 Replies View RelatedHave 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.
View 3 Replies View RelatedI'm getting a Sub or Function not defined error on the code below. It highlights the Replace Function as the problem, have also tried the substitute function too with the same result.
View 11 Replies View RelatedI need the equivalent of a double conditional in the Function SUMIF.
In my example (a test file is attached), I have used hypothetical stock trades as a test case.
Column B has the type of order (Buy or Sell).
Column C has the stock trading symbol.
Column F has the net amount of the transaction on that line.
I want to populate Columns I & J, where I & J are the total Buy and Sell cost for each stock listed in Column H.
So, for example I need to calculate the total "Net Amount" of "Buy" transactions for Stock ABC, and put it in Cell I2. I also need to put the the total "Net Amount" of "Sell" transactions for Stock ABC, and put it in Cell J2.
I'm sure that looking at the attached sample EXCEL workbook, will make it clearer than all my verbiage.
So, conceptually, I need to evaluate Columns B, C and F and put the sum of transactions in Cell I2 for those cases with "Buy" in Column B AND "ABC" in Column C.
If there is no syntax for a double conditional in SUMIF, then I'd be happy with any formula for Cell I2 that accomplished the tax.
I'm not terribly familiar with array formulas, but have used them on occasion if that's what's required to emulate a double conditional in SUMIF.
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:
View 6 Replies View RelatedI'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.
View 4 Replies View Relatedi have a very basic function using instr as follows:
ins = 0
Dim x As Integer
Dim y As Integer
findst = "["
finden = "]"
x = InStr(ins, oldstrgFd, findst, vbTextCompare)
y = InStr(ins, oldstrgFd, finden, vbTextCompare)
Debug.Print x,y
oldstrgFd in the example is populated with
[ClientName] Planning Workshop
[FeedbackLocation], [FeedbackDate]
[FeedbackPresenters]
I am expecting this the first positions of the square brackets, but x and y both return 0.
when ltoj is less than zero the following code enters a period in the cell instead of zero.
If ltoj < 0 Then ltoj = 0
Cells(r, 10) = Format(ltoj * 24, "##.##") 'writes data in correct format
Is there a way in which you can create an if function or similar to give a reason for why the cell of data has an error.
Basically i have some data in a table, and when a #num! error pops up id like to in the cell next to it give a sentence stating why this has happened.
E.G.
Cell A1 Cell B1
#Num! Number error due to 'x' being too small
Not sure how i can get this to work, i have tried a basic if function but that hasnt worked.
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...
I'm using this macro with no problems in another workbook. However after inserting the same code into another workbook, I get an errorfor sub/function not definded. Looking at the code it seems its highlighting what's in red below.
I've inserted the top section of the code, where I'm getting the error.