Find A Minimum Query
I have a set of worksheets all of which are identical and covered by first:last sheets
What I am trying to do is come to up with a minimum figure for cell G30
With the proviso that it is a minimum of 30 as some of the entries in G30 are less than that figure.
=MIN(first:last!G30) is not the answer as it gives an answer of zero.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Find Minimum Value Corresponding To Maximum Value
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). A small sample of what the data set looks like: 2008 Q22008 Q32008 Q42009 Q12009 Q22009 Q3 Atlanta771773768761749756 Austin770786784779776776 Baltimore947948949950950953 Boston1,6471,6601,6541,6481,6301,613 Chicago998998996985984986
View Replies!
View Related
Find Minimum Value From A Data Group
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.
View Replies!
View Related
Find Minimum With Criteria - MINIF
I have following problem I have some table - 2 columns - in first is text in second is value. example: A 10 B 5 C 3 A 15 C 8 D 7 A 9 I need to use formula, which shows me minimal value for choosen text. I tried matrix formula like {=min(if(text table=choosen text; second column;0}, but it's not working
View Replies!
View Related
Conditional Count (Find The Minimum Value In The Range)
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)
View Replies!
View Related
Find Minimum/Maximum Excluding Zeros
I have inherited some legacy templates (Standard, Leave and Exception) which cannot be changed. I need to summarise them (Total) selecting the earliest start and the latest finish. (Sample attached). The templates are 90 columns wide and about fifty lines deep so named ranges isn't practicle (I think). I'm running 2003.
View Replies!
View Related
Find 2 Corresponding Values & Multiple The Minimum
Look in Column A and find the cell that contains "Std A 1" and the cell that contains "Std A 2". Compare the corresponding values in Column B, and multiply the lowest value by .2 I've tried using various methods (find, if cell.value.instr) but I keep getting errors. Can anyone point me in the right direction?
View Replies!
View Related
Find Minimum Value In Column Corresponding To Specific Text
I have a table that contains various aspects of information about customer cases, and I want to replicate a user 'picking up' the case by a simple press of a button. Users have access to only one Country, so I want to be able to search a particular column for the lowest value, but check that the Country for that row matches the user's access. If it doesn't, I then want to find the next lowest value in the column, and this is what's perplexing me??? As mentioned, I want to click a button to trigger this, and therefore want to use VBA code.
View Replies!
View Related
Lookup Functions Find The Address Of The Minimum Value In That Range
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. =CELL("address",INDEX(B2:B7,MATCH(MIN(B2:B7),B2:B7,0))) 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.
View Replies!
View Related
VBA Solver: Find Minimum Value Of Cell By Changing X Cells With Constraints
I'm trying to use solver to find the minimum value of a cell by changing 5 separate cells. These cells are subject to a set of constraints that are formulae. On top of that I have a large number of these solve's to run (The spreadsheet is creating randomly distributed data to form a Monte Carlo simulation). Therefore I am referencing the formulas using the ActiveCell, Offset() function. For some reason when running this code none of the values are changed. Also I changed the UserFinish to false so I could see what solver was doing at each solve, It stops at the correct cell (one after the final cell) but without me having to confirm it each time. Here is the ....
View Replies!
View Related
Find And Match Query
I've got a sheet here with a master list of document numbers in Column A and a smaller subset in column C. I need to do is for Excel to scan column A for cell C1 and when it finds it in column A i need it to return the corresponding value in B column into cell D1. I need to do this for all column C.
View Replies!
View Related
Find Data That Matches Query
I have a spreadsheet with 3 columns: Name, Number and percentage. I need a formula to return the name that has the percentage and the 2nd highest percentage. the spreadsheet has 5 rows, not including the columns labels.
View Replies!
View Related
Vba Query: Attempt To Find A Missing Integer
I am trying to write a code enabling some calculations. As part of this code I want to find an integer in an excel range. I set the variable (integer) as Rvalue and wrote the following: Selection.Find(Rvalue).Activate which works fine as long as the find is successful. The issue I have however is when the variable cannot be found. I tried using the "iserror" function in vba but it appears that excel/vba does not recognise this issue as an error; I could use an excel formula (i.e. if(iserror(vlookup...) but I would prefer to find out how to handle the issue in vba.
View Replies!
View Related
If Minimum Below Zero Then Return Minimum
I have had a lot of luck finding what I need from the search areas, and I even found some information on the formula I am trying to build. The problem is I don't understand it and I need some help. First let me set it up for you. (I do not know the formula) If cell L125 is has a value >0.00, I need to locate the smallest value the range of cells C125:F125, I then need to subtract L125 from that number, otherwise enter nothing. This really has me baffled. I tried and I tried but it will not find the smallest value then subtract L125.
View Replies!
View Related
Create An Conection With MS QUERY To A Csv And Query Data
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run. I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
View Replies!
View Related
Query Parameters Which Takes The Date From The Cell Into The Query
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2. My query is this WEB 1 http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12 Selection=15 Formatting=None PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False DisableDateRecognition=False DisableRedirections=False
View Replies!
View Related
Query An Access Query With VBA Function
I'm trying to query a query in Access 2003, from Excel 2003. The query in Access looks like: AccessQuery: [SELECT VBAFunction(field1) FROM Table] The query in Excel looks like: ExcelQuery: [SELECT * FROM AccessQuery] I use the following VBA code in Excel to excecute the query: With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _ Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery) .RowNumbers = True .Refresh BackgroundQuery:=False iResultRowCount = .ResultRange.Rows.Count End With When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.
View Replies!
View Related
Edit Query Using Query Wizard
I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly. My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..
View Replies!
View Related
Value Corresponding To Minimum Value
i'm trying to reference a cell value in the column to the left of a "min" value: -A-------- B 1200------20 2400------15 2300------18 900-------22 i'm using the "min" function to select min value from column B (15) but only as a means to use the A column value (2400) in another formulahelpful
View Replies!
View Related
Minimum Value Within A % Range
I am looking to create a formula to determine the minimum value within the top 80% of a range, but relative to certain values in other columns. e.g. Column A Country Column B New Customer Column C Revenue I need to determine what the minimum value is for the top 80% of revenue in column C, but only including Country=UK in column A and New Customer=Yes in column B. This will tell me that the top 80% generate at least £x in revenue
View Replies!
View Related
Minimum Positive Value
in a column let'e say A I have the following values Total 11 20 7 -10 30 -5 -25 16 3 21 -8 I whant to have a variable = the row number with the minimum positive value which is in this case row number 9 and the column value 3
View Replies!
View Related
Minimum Of Range With #N/A
I searched and found a couple things dealing with zeroes or other errors, but not the #N/A errors. I have a range of numbers in A1:a100. To make this range linegraph-friendly, I have all blank or zero entries defaulted to appear as #N/A. Now the problem is that I would like to find the minimum number in the range, but the #N/A entries are messing things up.
View Replies!
View Related
Minimum Greater Than Zero
I have a table populated with equations. I need to write a function to find the smallest value in that table. However, I want to ignore the zero values. From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.
View Replies!
View Related
Defining A Minimum Value
If Average GMV (Cell A1) is less than 75% of Average BP (Cell B1), use Average GMV, however the Average GMV or Average BP can't be less than $600 million. Multiply the result by .01*1/3. Below are two equations I've created, however each of them only solves one part of the problem. =IF(A1<0.75*B1,A1*0.01*1/3,B1*0.01*1/3) =IF(B1<600000000,(600000000)*0.01*1/3,B1*0.01*1/3) Essentially Average BP would be used, unless Average BP was something like $900,000,000 and Average GMV was around $650,000,000. At this point Average GMV is 72% of Average BP and above the $600,000,000 minimum.
View Replies!
View Related
Return Minimum Value Above Zero
has any of you happened to make a function that finds the minimum NONZERO value in a data range? (i.e. all data are numbers, all of them are positive, but some of them are zero. The function should take the excel range as input, and return the minimum value above zero).
View Replies!
View Related
Conditional Minimum Value Of Various Cells
I have two columns: column C has job functions, say engineer, cook, driver, etc; column D has salaries. I want to analyse the salaries: what's the min/max,median, mode, average values for each of the job functions. (e.g., if data in column C = "driver", then corresponding salary to be included in the data to be analysed.)
View Replies!
View Related
Minimum Distance Between Points
I want to know the minimum distance between customers that I visited in each day. If i visit customer 1, 2 and 5 in one day, which distance is smaller? 1-2, 2-5 ou 1-5 and what is the corresponding value? I have in columns DATE, CUSTOMER 1, CUSTOMER 2, ..., CUSTOMER 5 and in each row I put the Day and 1 or blank for each customer (visited / not visited). I also have a mileage chart for each customer in another sheet which is the source for my distance data. I tried INDEX and MATCH but all I get is ZERO... I think it is comparing the same CUSTOMER.
View Replies!
View Related
Complicated Minimum Value Search
i have a spreadsheet that has 300+rows,and the details below b2 is the description and date, b4 to b29 are the item name c3 is total sales volume between c4 to c29, then another section starts from row 30 till row 57, row 58 till row 85 etc till the end (27 rows for each section) i need to search the minimum value in each section and highlight it as red. i know i can search the minimum value by using MIN(b2:B29), but how can i repeat it for each section?
View Replies!
View Related
Minimum Date With Two Conditions
The 1st problem is a formula to retrieve the minimum date with 2 conditions, 1 of the conditions is in the same sheet the other in a different sheet. This is 1 of formats that I have so far {=Min(If(And(Assigned!C$2:C$10,000),(Assigned!D$2:D$10,000=Teams!E2)))} and is not working. I also tried combining with sumproduct but still does not work. I have 2 sheets.On the Team sheet I have the associates names 1 time, on Assigned I have each associate names with all their sales and the date of their sales. I want to know each ones oldest date sale. I know I can sort and subtotal, but that is to much work. The 2nd problem might be easier this is the formula =Index(A2:J32,Match(E40,A2:A32,0),1). On E40, I have the max sale, I then what to pull the date of when that occurred. I have my dates formated as date. I want an exact date of the max sale.
View Replies!
View Related
Minimum Formula Greater Than To Zero
I have a range of weekly sub totals that get entered each week for the year. o8:bn8 As each week are entered I am trying to find the lowest week's production (using the =min formula) that is above 0 (weeks not yet entered appear as 0) the problem is that it keeps defaulting to the next column once a number greater than o is entered.
View Replies!
View Related
Return The Minimum In Column
I have columns of data as per below and if the data in Column A meets a certain condition I want it to return the minimum in Column B. Column A Column B BU1 5.45% BU1 7.00% BU2 10.00% BU1 4.67% BU2 3.50% So, if Column A contains BU1 I want to know the minimum of the BU1 %'s.
View Replies!
View Related
Minimum Non-zero Within Array Formula
I have a load of (multiply duplicated) categories (in col G) and values for them (in col P). Each category has more than one value, but they should be in roughly the same ball-park. I want to rank the categories in a pivot table, by value, not alphabetically. So I wrote this formula: {=MAX(P$2:P$10*(J$2:J$10=2)*(G$2:G$10=G2))} (I also am only interested in entries for which there is a "2" in column J as shown) This takes the category, finds all instances of the same category in col G and returns the max of all of them - so each row now has a "max value for this category" field. This is all great, but what would work a lot better would be grouping them by the minimum value in the column, but of course when I do this I get a zero (from any one of the hundreds of non-matches). I tend to get non-zero mins using small and countif but can't conceive how I would squeeze that into this formula.
View Replies!
View Related
Choosing Cell With Minimum Value
I am working with a spreadsheet that has quite a few products and each product has a column for each vendor of that product with the price associated with that vendor. So, I have five columns total: product, vendor 1 price, vendor 2 price, vendor 3 price, vendor 4 price. I want to have a 6th column that will show the preffered vendor, which of course is selected by the lowest price. I have been able to accomplish this by chaining IF statements together, but it is getting VERY cumbersome for more than 2 vendors. Ideally, I would like to tell Excel to look at the 4 price cells, choose the lowest one, and return a column number, which I can then associate with a vendor.
View Replies!
View Related
MINIMUM If With Multiple Criteria
I am trying to find the minimum date in column B based on the value in column A and the Maximum date in Column C. I already have a calculation for column C that ignores certain values, i am fine using that date.... On the first sheet, I have.... the "summary of data" The date in column C is the Maximum value ignoring 3 certain dates (10/10/2030, 11/11/2011, 12/12/2012) I used this formula to find that value. =MAX(IF((Sheet2!A$3:A$16=A4)*ISNA(MATCH(Sheet2!C$3:C$16,{47766,41255,40858},0)),Sheet2!C$3:C$16)) Now, based on that I need to find in column B the Minimum value based on both the value in Column A (item) and the maximum date already found in colum C (Max)
View Replies!
View Related
Minimum Plus Adding Formula
Average in Cell J8 : J73 Handicap in Cell I8 : I73 What I’m trying to achieve is the Lowest Average Plus handicap – 1 = total Example Cell J8 = 105 J9 = 169 J10 = 126 J11 = 155 Cell I8 = 45 I9 = 35 I10 = 54 I11 = 21 Result 105 + 45 =150 – 1 = 149
View Replies!
View Related
Pick The Minimum Numbers
I need a function like a sumif, but rather than adding together numbers, I want it to pick the minimum. For example, if I had a list of prices for various products, I want the formula to go and find the lowest price for a particular product.
View Replies!
View Related
Minimum Difference In Dates
I am trying choose the nearest date, from todays date out of a small array of numbers, and then also find the number of days between them. Additional to that, i would like it to ignore a date if that is in the past i.e. <now(). So I have a list of dates in column a, and i would like it to show me the number of days till the next closest 1, considering it hasnt passed already. I have tried many different ways and itteration of IF statement to solve this 1 but just cannot do it.
View Replies!
View Related
|
TRACKER
