Lookup Functions Find The Address Of The Minimum Value In That Range
Sep 19, 2009
I have a range of cells (say A1:D8).
I wanna find the address (say $B$2) of the minimum value in that range.
This command does it but it only works on a column. it does not accept a range spanning more than one column.
this will tell me the address of the minimum value.. but in a column... I need a range of many columns.
So I tried converting things to a Table.. but it never worked. basically the MATCH command is my problem. It only accepts single-column-ed ranges.
If I have a range of data say all numeric and range form cells A1:A20. How would I find the minimum value except A4 where A4>0?
I know I can use the 'MIN' function and highlight all the data bar A4 (min(A1:A3,A5:A20) but i was hoping for an actual way so I could later fix the column such that first A1 is the first to be omitted A2 is the second and so on.
I am trying, without success, to create a formula that will refer to a column of data and do the following in one step:
1. Find the minimum value in the range, and then 2. From that minimum value, count back “up” the column the number of occurrences, including the minimum value, until a zero is reached. The zero should not be counted.
For example, if the values in A1:A6 are 1, 0, 2, -1, -2, 1, the minimum value is -2 and the count would return 3. (i.e. 2, -1 and -2)
I'm looking for a formula to find the minimum value in a non successive range of numbers (excluding zero values).
My sheet contains four fields of numbers (#1, #2, #3 and #4) and than start over again with number #1, #2, #3 and #4 etc. This goes on for approx. 25 4-column blocks. A simple MIN-formula or SMALL-formula doesn't seem to do the trick as it takes all values into account, not just the #1 or #2 or #3 or #4 values.
Example (cells A1..A24): 999-999-999-999-110-120-130-140-111-121-131-141-112-122-132-142-0-0-0-0-113-123-133-143
The minimum value of #1 (excluding the zero's) would be 110, the minimum value of #2 would be 120, #3 would be 130 and #4 would be 140.
Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.
I am performing a lookup using the populare user defined function nlookup, which does not take a lookuparray argument to find lookup values. But I only want to return lookup values that fall into a certain array. To accomplish this, I figured out that I need a way to specify a condition of the type "if cell address of lookup value falls within range"
It should be possible if I find a way to return the cell address of the nlookup value, but as nlookup is not limited by a lookuparray argument, so I was not able to use the address/index/match that come up in similar questions.
I am having troubles with using two methods together. I know how to find the address of the first or last occurance of a MAX in a range. I also know how to create a MAXIF function using criteria to determine what to consider for the max calculation. I do not know how to return the address of the result from the MAXIF.
I am using Office 2003 on WinXP.
Ultimately I want to take the address of the first result of a MAXIF and use it to do a lookup for a corresponding value. I'm not sure if I will be able to do this with a combination of functions, or if I will need a macro.
I'm trying to calculate a monthly average from a set of daily values in a separate sheet. I managed to use some nifty code to return the address of the first value in the month, and the address of the last value in the month.
I tried to create an AVERAGE formula to compute the average between these two looked up address and I'm getting various errors, including #DIV/0 which doesn't make sense to me. Here is the summary of the code I'm using, in generic terms.
Formula to lookup first address in month: =ADDRESS(MATCH(A57,'Sheet2'!$A$1:$A$2000,0),12,1,TRUE,"Sheet2")
Formula to lookup last address in month: =ADDRESS(MATCH( DATE(YEAR(A57),MONTH(A57)+1,0),'Sheet2'!$A$1:$A$2000,0),12,4,TRUE,"Sheet2"))
A57 is the cell in Sheet1 containing the relevant month, for example in this case it is 4/1/2008 (displayed as Apr-2008). Column A in Sheet2 contains the daily dates. Column 12 in Sheet2 contains the relevant data that I want to average.
I've tested the two address lookup formulas and they are working fine. I assume I'm missing some sort of small conversion such that the average formula is not reading the ADDRESS formulas as addresses.
I am struggling to find a macro which can look at a name in column 'BT' and search it in the address book of Outlook to then place the email address of that person in column 'ED'
There are 35,000+ people in the address book and there may be over 5 email addresses for one name, so is there any way a message can appear for the user to select which email address is correct if there is more than 1 contact for that name?
As far as I can tell, the formula works just fine. If you look in the Name Manager and check on that Name, the highlighted box shows up exactly what it should and if you do a simple MAX test, it displays the correct MAX value from that range.
The problem comes in when I try and add it to a chart. Instead of a displaying all the values in the range, it displays nothing. Doesn't flag up any errors, just nothing.
The reason I chose to do it this way is that the table contents could change on a daily basis and so could the position of the row and I don't want to have to keep changing the references in the graph so make sure the right data is being displayed. Is it just that charts don't play well with the INDIRECT function?
I have searched your forums and thought I had found a sufficient answer but could not get the vba to work. So any help is greatly appreciated. I am trying to determine a max value from a list then put that value in a cell. Next I want to determine how many times and on what day that max value occured. From there take the value and concatenate them adding a "," between them I have attached an example. I would like the values placed in cells F1 and H1 (the other is a min value and when it occurred)
No matter what changes in this string, these four digits (9, 10, 11 & 12) will always be in the same position.
What I would like to do is (I think) use the FIND funtion and then perform a VLOOKUP against an already defined table/range which identifies the actual location in the destination cell. Will FIND be the easiest for me to do this our should I use something else?
The what to interpret characters 9-12 (using 3600820040030500050005271000 as an example):
4003 is the location information which consistantly breaks down:
4 = Region Name 003 = Office / City (001 thru 024)
There are 7 possible regions with 24 possible Offices/Cities. I want to believe I can make this lookup happen using VLOOKUP tables that break them down - but I just don't have the brainpower to identify those 4 digits and then break them out for a look up.
Purchase Date (column A) Sales Date(column B) Quantity(column C) Name (column D) Gain/loss (column E)
The spreadsheet is sorted by name in ascending order and also by quantity in ascending order.
Assuming row 1 is the heading. If D2 (name)= the same as another D cell and it's quantity i.e. C2 is negative, while the other D cell with the same name as D2 has a positive quantity i.e. corresponding C cell, and if the date in B2 is greater than date in column A of the other cell where it's D cell matches D2 and has a positive quantity, then in I would like "possible" to display in F2.
I have included an attachment to better illustrate what I have described above, because I am not sure if I am clear enough.
I have two spreadsheets (the samples for which I have shown side by side in Sheet 1 of the attached file).
Spreadsheet 1 is about 30,000 rows and too large for me to change the formatting and structure.
Spreadsheet 2 is the output I need and the format is required by other stakeholders.
In spreadsheet 1 I want to sum quantity in stock for Type 1, Type 2 and Type 3 for each product and allocate it to spreadsheet 2 according to the month in which the product expires. For example, there will be a total of 92 units of product 413302 which will expire in Nov, 2014. Therefore I want 92 to be placed in cell N6 of Spreadsheet 2.
Unfortunately the product number is not unique - there are multiple sub products in spreadsheet 1 but they all have the same quantities of stock. The sub products are referenced in other parts of the report so I can't consolidate by Product Number. This also prevents me from using the SUMIFS function as it will duplicate the number found in the sub products.
What I need, perhaps in a combination of functions, is to find the first instance of product 413302 in Spreadsheet 1 that is expiring in Nov 14, sum the product types and give the result in cell N6 of Spreadsheet 2.
I'm at an impass and need some help please. I'm trying to create a spreadsheet that will look up UNSORTED data in Column A and return the value of the corresponding Row data in the adjacent column. Can this be done? It is my understanding that LOOKUP functions only work with sorted, ascending data, but this is not possible with the data I'm trying to analyze.
(eg. COLUMN A - contains unsorted list of names COLUMN B - contains numbers indicating skill level of the person in the next column
I need the formula to look at column A, pick out "Joe Smith" and return the number value from COLUMN B)
I have multiple IF functions in a formula and found out that the maximum allowed is 7 and should use Lookup instead. The formula is to calculate the Present Value of an amount with the corresponding interest rates and number of days left.
I am having trouble with large data sets of rents by market: trying to find the peak rent in a time-series, and then look for the next trough rent in time. I tried using the Newsletter 3 techniques which feels like the right direction, but I am not getting the nesting right or some of the parameters in the functions.
So, within each time-series I need to find the MAX value (peak rent), wherever that is in the array. Then I need to look to the right of that exact MAX value and find the exact MIN value (trough rent), whereever that is in the array (again, to the right of the MAX value).
What I would like to do is have a master workbook that I can import different .csv files into as a new worksheet. Then calculations will be run on the values that are imported. My goal is to select a column and have corresponding list update the values. After that formulas will run on the calculations. I have got the import csv file down using VBA. The problem I was running into was with the Data Validation. Since I was overwriting my existing sheet I would get a #REF error because the link was broken. So I have worked my way around this for the list using the OFFSETSHEET Function:
VB: Function SHEETOFFSET(offset, Ref) [COLOR=#0000ff] ' Returns cell contents at Ref, in sheet offset[/COLOR] Application.Volatile With Application.Caller.Parent SHEETOFFSET = .Parent.Sheets(.Index + offset) _ .Range(Ref.Address).Value End With End Function
This returns a value to a row in my mater sheet and I reference this for the data validation list.
However, I get the same problem when working with the HLookup function, the #REF error occurs. So far the only way I have figured out to work around this is to create another sheet that dynamically updates its values using the same OFFSETSHEET function, and my master sheet then references it.
I've got an indexmatch that works great =IF(ISERROR(INDEX(accountstaff,MATCH(B$20,INDEX(accountstaff,,1),0),MATCH($ A55,INDEX(accountstaff,1,),0))),0,INDEX(accountstaff,MATCH(B$20,INDEX(accou ntstaff,,1),0),MATCH($A55,INDEX(accountstaff,1,),0)))
But I need to incorporate IF statement based on varying levels of revenue and think VBA will be less cumbersome, but don't know how to combine else if and index match.
I am trying to accomplish this: IF B$20 (which is a dollar amount is = X, then index, match.... IF B$20 is > Y but < Z, then index, match...
I have been trying to solve this problem for the past couple of weeks and I have not been able to do so. I have come to do the operation manually, and it really takes a lot of my time, so if you guys could help me, I would really appreciate it.
Basically the problem is this:
In column "A" I have what I call point numbers for all the data entries.
In column "B" I have the station (or location) at which I vary slightly a parameter (represented by column "E") to give me a value shown in column "D" (the "Z" value, as shown in the sheet).
So basically you can see that my station 0 has 11 different "E" parameters (0, -1, -2.62, -5, etc) and each of them has a "Z" parameter (390.76, 390.775, etc).
What I am trying to do is to come up with a table that shows a summary of the following:
A column with each of the stations (0, 20, 40, etc -- notice that I have some weird numbered stations like 411.89).
Another column with the "E" parameter corresponding to the lowest (or minimum) "Z" parameter within the station range.
And a final column with the minimum "Z" parameter corresponding to its "E" parameter, which is included in the station range
You can see in yellow the way I have been doing this and you can see that it would take me so much time to accomplish the task.