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 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
Averaging But Excluding Zeros
I have a list of numbers that I want to average. Problem is, some of the numbers contain zeros and i want to exclude these entries. So, if I had a list of 6 values and 3 were zero, I'd want to sum the list and divide it by 3 (not 6).
View Replies!
View Related
Create List Excluding Zeros
I have a column with values, say column a. In column a are several rows with values equal to zero. I would now like to create a new list that omits the values that are zero, e.g.: original list: 23 0 54 76 0 0 new list: 23 54 76
View Replies!
View Related
Excluding Zeros When Calculating An Average
I have a one column of numbers...let's say consisting of six rows the following numbers 12,0,14,25,0,9 if I average this it comes out to 10 how can I average the same rows but exclude the zeros in the outcome(is there a formula to do that?) I would like the answer to be 15.....I still need the zero's in the rows however
View Replies!
View Related
Finding Maximum Value While Excluding Some Values
I have a row of data with the cells I want to find a MAX for are separated from each other: a1=300 d1=500 g1=800 j1=10,000 I want to find the MAX value for a1,d1,g1,j1, but exclude any value over 1000. So instead of =MAX(a1,d1,g1,j1) returning 10,000, =???(???) will return the next highest max of 800.
View Replies!
View Related
Match Criteria According To MINIMUM And MAXIMUM
I need a macro that will search MIN and MAX columns and identify which horse has the maximum number in both the MIN and MAX columns an example is highlighted yellow in the attached spreadsheet. If there is a selection i want the macro to keep the horse that matches the criteria along with the race date and time e.t.c (header) but delete all other horses in that race. If there are no selections that meet the criteria in a race i want it to delete all details for that race including headers and go to the next race.
View Replies!
View Related
Limit Returned Value To A Maximum/Minimum
Given this formula =(H2+( SUM(K53:DB53))) which simply takes an existing value and adds to it the sum of a series of values. I am happy if the value returned is any number less than 28. However if the value returned from the above formula is >28 I want the number returned to be shown as 28.
View Replies!
View Related
Lowest/Minimum Divided By Highest/Maximum
I have a sheet of about 15000 rows made up of about 1300 groups( events) and 40 columns, a miniature of which is attached. In column1 I have the event identifier,column 2 contains a score or rating for each contestant in each event.,in column 3 there is a code for each competitor,either orange or pink.There will be at least 1 orange and 1 pink in each event. column 4 is the one I want to create by formula,the lowest pink in each event divided by the highest orange. I have titled this column the spread. I have filled column 4 manually to illustrate what I mean.
View Replies!
View Related
Count Daily Minimum/Maximum Values
the macro will track daily min/max values, and keep a count of the times these values occurred in a separate sheet in the workbook. At first I thought simply setting up a macro to look at a predefined range of rows to find the min/max values, but some days may be missing data; therefore, I need a macro with the logic that looks for the min/max value appropriate to each specific day (which is the tricky part). Below is the detailed explanation of the process. 1)The "Main" sheet, Column A, contains days 01/05/1999 – 01/29/1999 (days are already in numerical value format). Begin in A1 and this will be the first daily value. Find the Max Value in Column D corresponding to the numerical daily value of A1. 2)Once Max Value found in Column D, Column B of same row will have the time the Max Value occurred. 3)Count time Max Value occurred in “Time Count” sheet Column C of corresponding time. 4)Move down to next daily value. Continue this process to last row in sheet. For example, “Main” Sheet, day 01/05/1999 (numerical daily value 36165), Max Value is 1.1835 found in Cell D12. B12 has the time at 2:45. A “1” is then placed next to 2:45 or C14 of the “TimeCount” sheet. If the next day’s Max Value occurred at the same time, then a 2 would be placed in C14...........................
View Replies!
View Related
Return Data Corresponding To Minimum & Maximum
Please see attached file which is data for horse races (this is a small example of the data i will be working with). I need to find the largest values from the MIN and MAX column (shaded Grey) range for each race. I want to be able to add a function that will allow me to quickly identify which horse has the largest number in both the MIN & MAX columns example on spreadsheet is highlighted RED. It would be handy if it would inform me if the criteria has been met by highlighting it or by placing some text value in an adjacent cell on my spreadsheet i have used the example "Y". It would also be beneficial if that race be deleted in its entirity if there are no horses which meet the criteria above or similary if all other selections within that race be deleted if ther is a horse which meets the criteria. If there is a selection within that race i will need to keep the row that includes the heading which is coloured blue on the attachment. I will be working with 20,000 or more selections at a time so it is important that they can be identified quickly for ease of use. I have tried the functions i know such as MAX and LARGE but this requires me to do alot of work when you consider the ammount of data i'm required to work with.
View Replies!
View Related
Color Maximum & Minimum Value In Range
I use code as below to find max or min data row, data is in not integer, but match doesn't work, I got error 1004, "Match function's properties can't be accessd". Sub SelectMaxMin(ByVal vSheet As Worksheet) Dim i As Integer, oRange As Range, iRowMax As Integer, iRowMin As Integer For i = 4 To 23 Set oRange = vSheet.Range(Chr(64 + i) & 6 & ":" & Chr(64 + i) & 82) iRowMax = WorksheetFunction.Match(WorksheetFunction.Max(oRange), oRange) + 5 iRowMin = WorksheetFunction.Match(WorksheetFunction.Min(oRange), oRange) + 5 vSheet.Cells(iRowMax, i).Interior.ColorIndex = 40 vSheet.Cells(iRowMin, i).Interior.ColorIndex = 35 Next Set oRange = Nothing End Sub
View Replies!
View Related
Copy Values Between Minimum & Maximum
i have found this code on ozgrid to search between a min max value, trouble is it only selects the first cell it finds. How can it be modifide to copy each value in between the min max set say to sheet 2? Option Explicit Private Sub CommandButton1_Click() Dim strNum As String Dim lMin As Long, lMax As Long Dim rFound As Range, rLookin As Range Dim lFound As Long, rStart As Range Dim rCcells As Range, rFcells As Range Dim lCellCount As Long, lcount As Long Dim bNoFind As Boolean strNum = InputBox("Please enter the lowest value, then a comma, " _ & "followed by the highest value" & vbNewLine & _ vbNewLine & "E.g. 1,10", "GET BETWEEN") If strNum = vbNullString Then Exit Sub On Error Resume Next lMin = Left(strNum, InStr(1, strNum, ",")) If Not IsNumeric(lMin) Or lMin = 0 Then...................
View Replies!
View Related
Calculate Minimum & Maximum Of Range
Is there a easy way to calculate the MAX or MIN of column B dates to column A items that have say many different dates in column B? see attached excel file to show what I am looking for. How to calculate the MIN value in column "B" of the items in column "A" with formula in column "C"...
View Replies!
View Related
Maximum & Minimum Axis Values From Chart
how I get the maximum and minimum values of the axis from and excel chart? What I mean is I want to specify a chart, and output in the same excel worksheet the values for the maximum and minimum of the two axis. I have tried various searches, but cannot come up with the answer, although I have a sneaky suspicion it in here somewhere.
View Replies!
View Related
Maximum And Minimum Formulas Based On Date Range
I have a spreadsheet with two columns - column A contains dates and column B has the corresponding data (always numbers). The data is not uniform (i.e there may be lots of entries in any given day and none over the next few). The data is always sorted in chronological order. I want to set up a summary table to show the min, mean, median, max for each week. I have set up an array to calculate the mean, but I am having trouble working out a good way of gettting the others short of having a set of columns that manually define each cell...for instance =min(b3:b27) or =min(b28:b30).
View Replies!
View Related
Lookup Maximum & Minimum. Return Corresponding Row
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)
View Replies!
View Related
Minimum & Maximum Of ScrollBar Back-to-front
When I draw a vertical Scroll Bar and set the Min and Max in Properties, the Min gets set at the top of the Scroll Bar and the Max at the bottom. This seems backward to me, but it's probably an issue with Microsoft's programming. To get around this, I've entered the Max value in the Min and the Min's value in the Max. This allows the Max value to be at the top of the Scroll Bar and the Min at the bottom, but I was wondering if this is anathema in the world of coding, and could I be setting myself up for coding problems down the road?
View Replies!
View Related
Return Only Unique Text From Another Sheet Along With Correlating Minimum And Maximum
I have, on one sheet, a list of incumbent employees that includes the following columns: Job Title [text] and Salary [number]. I am trying to automatically populate a second sheet with columns designating: A list of each unique job title (no duplicates), the maximum salary corresponding to each unique job title, and the minimum salary corresponding to each unique job title. Lastly, I'm an attorney dealing with confidential client data, so I'll have to go through a lot of trouble to post an example... but I'll do it if necessary.
View Replies!
View Related
VBA To Average Range Starting From Minimum Value & Ending At Maximum Value
I am using Excel to use stock ticker symbols such as FDX "Fedex", to return past volume and price daily performance found in Google Finance that will be manipulated with the current VBA I am working on that will result in a trading pattern. Each trading pattern will happen at a different range in the same column. I will be inputting hundreds of ticker symbols daily so using VBA and not having to enter formulas manually in each is greatly preferred as this will save me lots of time. I am stuck trying to find the average value of a range starting from the minimum to the maximum value. For instance, Range("H10:H20") may have a maximum value at range("H12") and a minimum at Range("H18"). I would like the average of Range("H12:H18"). Sub Fnd() Dim MaxStartPriceRange As Range Dim MinStartPriceRange As Range Dim MaxPriLocation As Double Set MaxStartPriceRange = Range("h11:h21") MaxPriLocation = Application.Max(MaxStartPriceRange) Set MaxStartPriceRange = MaxStartPriceRange.Find(MaxPriLocation) Dim MnPriLocation As Double
View Replies!
View Related
Set Chart/Graph Minimum/Maximum Scale To Nth Smallest & Largest Value
I am trying to manually format a few graphs in my sheet. I, however, leave the major units of the axes to be automatically calculated. The code that I use is given below: ActiveChart.Axes(xlValue).Select With ActiveChart.Axes(xlValue) .MinimumScale = Range("Min").Value .MaximumScale = Range("Max").Value End With Now what this does is that it sets the maximum of the scale exactly equal to maximum of the range being plotted and hence a few parts of my graph overlap with the border of the plot area. Is there any way in which I can round the max and min scale of y-axis to the next highest and lowest major unit respectively or may be add and subtract a value proportional to the major unit of the y-axis. I cannot add a constant in the code given above since I that does not suite my requirements.
View Replies!
View Related
Finding The Minimum Time And Maximum Time
NameTime InTime OutAlan08300930Alan10001030Alan12301630Tony11301230Alan09450950Tony10301115 I would like to find the minimum time in and maximum time out for each person. The data type of Time In and Time Out are general. I.E NameTime InTime OutAlan08301630Tony10301230 Therefore, I would like to know what function in excel will enable me to perform such task. Furthermore, can this function use with VBA?
View Replies!
View Related
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 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 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
Find The Maximum Value
I wanted to know if there was a way to find the Maximum value given a number of conditions are satisfied. Similar to COUNTIFS and AVERAGEIFS, where you state the data range to use, the condition's range, the condition, then the next condition's range, the next condition, etc....
View Replies!
View Related
Find MAXIMUM Of Text
I want to know how to get the max of text. I am having sheet which is showing Codes for deliverables as A,B,C,D as an entry. I want to pick the latest code, means i want search the row for Max value, i.e is D and display it in cell.
View Replies!
View Related
|
TRACKER
