Return Row Number Of Conditional MIN Array Function?
Jun 23, 2014
I have the following data; B1 and B2 are entered by the user and are named 'heat' and 'race' respectively. Conditional Array Formula Example.xlsx I'm after the ROW number that matches the minimum time (col C) for the heat and race combination shown - in the example below (heat: A, race: 100m), I'd like row '5' returned (or, better still, row '2' of the array A4:D11), as that's the lowest time for Heat A, 100m.
A
B
C
D
[Code].....
but this dosesn't exactly seem elegant, and doesn't work!
ie I simply want the row number (ideally relative to the range) for the lowest time for any given inputs 'Race' and 'Heat'.
View 3 Replies
ADVERTISEMENT
Aug 5, 2013
I have a grid like follows (how to format):
2013-08-16-5.00 0 0
2013-08-16-2.0-19,902 -20,734 -21,429
2013-08-16-1.011,431 11,907 12,297
2013-08-160.0-0 0 -11
2013-08-161.0111 153 140
2013-08-162.031 61 60
[Code]...
Basically I want to return an array (to pass to another function) containing all data that matches my search. E.g. I want to retrieve an array of the data that matches 2013-09-20. Is the only way to do this a VBA func?
View 5 Replies
View Related
Apr 11, 2014
I need a look-up function that can return a value from an array that can be 3 to 7 rows in height. I want the function to always look at the last row in the array.
This is how it looks:
Column A Column B
blank row
1000
A
B
C
SUM 15
blank row
In this case I want the function to return 15 if I search for 1000. The problem is that there are not always a fixed number of rows beneath the searched value.
View 1 Replies
View Related
Jan 3, 2008
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
View 4 Replies
View Related
Apr 12, 2013
I would like to write a formula to return a value from a column that relates to an array within which my lookup value exists. See the tables below:
ID
V1
V2
V3
105
27
3
149
[Code] .......
The tables above are on different sheets within a workbook. I would like to write a formula that returns the "ID" numbers from column "A" in the first table, based on values in columns B-D in that first table, into column "C" in the second table. For example, in this case, the ID number that corresponds with the value "12491" would be "109", since 12491 corresponds with 109 in the first table.
FYI, the "Value" numbers in the second table are calculated based on their rank (high-low) within the matrix in the first table.
I tried the INDEX-MATCH function, but it doesn't seem to work if I'm trying to find a value within a 2-D array- it only works if I'm looking in a single column.
View 2 Replies
View Related
May 8, 2013
I need to find a way to find an array in a tab to use for an index/match function I have.
this is what i would normally use: =index(tab_array,match($a2, tab!$a$1:$a$1000,0),match($B$1,tab!$a$1:$zz$1,0))
This formula would usually work fine when I know that within the tab, the array in which the row match is searching doesn't change. However, that array may change in the future, to say column H, without my knowing as it is a database that gets automatically populated from an upstream system.
Is there a way to search for an array within an index/match?
View 1 Replies
View Related
Jan 10, 2014
Assume you have a rectangular range. Say A1:M18. There are values in some random cells (any number of cells) within the range. The rest of cells are blank. In cell M19 (ie cell below the bottom right corner of the range), I need a excel function that returns the nearest (ie physical location) non blank cell value. I have searched the internet for weeks. The closest formula is the following (see below) but it is flawed as it returns zero if there are values in rows below the row of the nearest cell with non blank...and also zero if there is value in a column greater than the column of the nearest cell with non blank.
Closest formula so far:
INDEX(A1:M18,LARGE(IF(ISBLANK(A1:M18),"",ROW(A1:M18)),1)-ROW(A1:M18)+1,LARGE(IF(ISBLANK(A1:M18),"",COLUMN(A1:M18)),1)-COLUMN(A1:M18)+1).
And the function should be relative not absolute as the range can be extended..but the location of the formula remain same relative to the array size. (ie. always at below the right bottom cell).
NOTE: Ignore the special case where two non blank cells are the nearest. That will not happen.
View 5 Replies
View Related
Jun 14, 2008
I looking for a macro which will help to open a file with current week number in name.
The problem is week 1 is starting on 30/03/2008 (finacial year) and ends on 28/03/2009.
I've made two dimensional array (week number, weekday) with all the dates from that period.
I have problem with code to search through the array for given key, return index and write it into variables.
For now my code looks like:
Sub week()
Dim i As Long
Dim j As Long
Dim k As Long
Dim week(51, 6) As String
View 5 Replies
View Related
Mar 16, 2009
I am wondering about the best syntax for using a VLOOKUP return as the row number in a CORREL function. I want to create rolling correlations from today's date. I have a VLOOKUP function that will return the row number corresponding to the chosen day's date. I now need to use that returned value in the CORREL function. That is, I would like it to look something like:
=CORREL($E$VLOOKUP(today-90,AD5:AE3143, 2):$E$VLOOKUP(today,AD5:AE3143, 2),$E$VLOOKUP(today-90,AD5:AE3143, 2):$E$VLOOKUP(today,AD5:AE3143, 2))
When I enter this, I am told that I have an error. Is there a better way to nest this vlookup?
View 3 Replies
View Related
Dec 15, 2006
I am using a MID fn to extract a single digit from a numeric string. I then use an IF Function to return a value based on the extracted digit. My IF statement works fine on manually entered digits but doesn't recognize my MID fn result. What am I missing?
View 6 Replies
View Related
Feb 27, 2014
I have two columns in a spreadsheet that I want to use an IF function with. In the first column, I want a pick list containing 5 text options. Depending on which option is chosen, I want a number to auto-populate in the second column.
The numbers are important, because I want to repeat this pair of cells with slightly different text values in the second pair, and then to have a column that multiplies the two resulting numbers and conditional formats the result.
View 14 Replies
View Related
Jul 16, 2012
I'm trying to figure out how to write a VBA Match function that can look for multiple criteria and return the row number of a successful match.
I have about 255,000 rows of data on the worksheet "Filtered". Column B contains my Item Number and Column D contains the supply source. I want to find the row where ItemNumber and SupplySource match my variables and then return the value from Column C.
I can do a match for one criteria, but where I'm having problems is getting it so the two matches are on the same row.
View 4 Replies
View Related
Mar 17, 2009
I have a table with client ID in column A (range A2:A200) and qty ordered by those clients listed in column B (range B2:B200). Clients’ ID numbers range from 101 to 999.
Except through filtering, how to return the sum of quantities ordered for stores with ID number between 100 and 199 ONLY?
View 9 Replies
View Related
Jan 26, 2010
I need to figure out how to match three values on the "source info.xls" file attached to the "PFG FILE.xls" that is also attached. In column A in the "Source info.xls" file the formula will need to match the yellow column, then the green column, then the red column. keep in mind that the attached files are just a small subset of data. The "PFG File.XLS" is actually 150K rows long.
View 5 Replies
View Related
Aug 5, 2014
I am trying to use some vba match function code to return the column number of the matching date. The date will be stored in a date variable.
Every time I run this code I normally get a match error even though the date is in the worksheet and the variable matches that date.
See below:
[Code] .....
View 5 Replies
View Related
Sep 30, 2009
I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:
Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula
The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:
{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}
This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.
View 2 Replies
View Related
Feb 11, 2008
I'm trying to use an array to carry out string function on a range of excel cells.
Here is the code I am using
Dim arrXl As Variant
arrXl = ws.Range("F1:F" & ws.Range("D1").End(xlDown).Row)
For i = LBound(arrXl) To UBound(arrXl)
MsgBox (LBound(arrXl))
'If 1st char is different from 3rd char then remove all of string after 1st char
If Left(arrXl(i), 1) <> Mid(arrXl(i), 3, 1) Then
arrXl(i) = Left(arrXl(i), 1)
End If
'If 1st char is different from 7th char then delete string after 5th char
If Left(arrXl(i), 1) <> Mid(arrXl(i), 7, 1) Then
arrXl(i) = Left(arrXl(i), 5)
End If
The lbound function returns the value of 1 as the lower bound, I do not have "Option base 1" set so I was expecting the lbound value to be 0. The first 2 cells in the F column are blank so this may have something to do with it, I am unsure if cells in excel can be null if they can be null one cell may be null and the other may be a zero length string but I am unsure about this.
The ubound function returns a value of 487.
The code breaks when I try to access an element in the array so it breaks on the line:
If Left(arrXl(i), 1) <> Mid(arrXl(i), 3, 1) Then
and returns the "Subscript out of range" error message.
View 5 Replies
View Related
Aug 6, 2009
I need to multiply an array of logical results ( returned as {1,0,0,0,1,0 et.}) with a text array (a reference column) and return the text in the reference column in case the value in the logical array is 1.
View 3 Replies
View Related
Apr 13, 2007
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies
View Related
May 14, 2013
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]....
View 9 Replies
View Related
May 13, 2013
I am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)
right now it looks like:
If (C10
View 1 Replies
View Related
Nov 16, 2005
how to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array formula
Example:
Name Replied?
Bob N
Brian Y
Robyn N
Rachel Y
From the table above if I test for "N" I'd like the formula to return a list
of names, in this case Bob and Robyn.
View 10 Replies
View Related
Apr 16, 2014
I have a challenge, which i have attached a spreadsheet. It's probably easier if you look at the spreadsheet, rather than me trying to explain. I've added some notes for reference.
I've tried to use an IF/vlookup function but I cant seem to make it work.
Attached File : ans.xlsx‎
View 9 Replies
View Related
Feb 8, 2014
I'm looking to use the percentile formula given a condition. The percentile formula as I understand it is percentile (array,k), where K is the percentile I want.
What I want to do is select a column for the percentile, but only do it for values above zero (I don't want to get into the full explanation as to why but I cannot cheat and just sort the columns).
The context is that I was doing some averageif calculations previously and want to remove outliers (top 5 and bottom 5% of data).
View 1 Replies
View Related
Nov 28, 2008
Attached is the file where I want to derive Min Date of seq A and asset Type Char.
SeqAsset TypeStDateEndDate
AChar21-11-200828-11-2008
BEnv22-11-200829-11-2008
CProp30-10-200830-11-2008
AChar03-11-200801-12-2008
AChar04-11-200801-12-2008
AChar05-11-200801-12-2008
AChar06-11-200801-12-2008
AChar07-11-200801-12-2008
I want to find the Minimum Date of Seq "A" and of Asset type "Char". I used following Array Formulas but showing the correct answer 30-10-2008MIN(IF(A2:A9="A",C2:C9="Char"),(D2:D9)) But Istead of 03-11-2008 it's showing 30-10-2008 date of seq C and of asset type Prop
View 2 Replies
View Related
Aug 21, 2013
I am trying to use conditional formating that will highlight any cell in the range M2:P9 are greater than 0 AND where M1:P1 (text) does NOT equal Q2:Q9 (text).
View 3 Replies
View Related
Jan 31, 2013
I want to find a number in my array and return the value in column A. Trouble is if the array goes past column R I get the wrong Answer. the formula I am using is
=LOOKUP($C$22,$B$2:$AC$18,$A$2:$A$18)
Some cells are blank I am trying to draw a raffle.
Here is my data and formulas below:
The lookup of value 209 produces correct results when I look up as far as column R after that I get the wrong answer.
A
B
C
D
E
F
G
[Code] ....
View 2 Replies
View Related
Dec 20, 2013
The short version:
(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25))) evaluates out to series of 340 True and Falses. I need to be able to pull the nth True, and then somehow index that against AH2:341.
I'm working with a record of 53,000 entries with latitudes and longitudes and I need to cross compare them with 341 locations in the United States.
I've already done some of the heavy lifting, which supported narrow an initial list of 85,000 records to at least recognize which records where within any of the 341 points. (Less arrays makes my computer happy.)
This formula gives me the count of locations each entry is within one of the 341:
=SUM(IF(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25,1,0)) {CSE} & copied down 53k times
The actual return range is AH2:AH341 for the location number.
Now, I need to be able to list each of the 341 they are close to. The max count is 17, so I'll only need to list out 17 columns.
Maybe something like =LOOKUP(1,1/(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25))),$AH$2:$AH$341)
but this only returns the first match. How do I return the 2nd, 3rd, 4th, etc?
Because of the sensitive nature of my data, we can use a much simpler array which I'll adapt; I'm mostly trying to understand the logic or formula that should be used.
Return Nth Match.xlsx
View 3 Replies
View Related
Mar 10, 2014
I have the attached example workbook and I'm trying to lookup a date from a list based on a project start month:
- in cell D8 I want to be able to return the date that corresponds to the project month in row 5
- the dates are in column A
Example WB 1.xlsx‎
View 3 Replies
View Related
Aug 1, 2008
My first attempt is to return a simple Array of ColorIndexes.
View 10 Replies
View Related
