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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
Count Conditional Format Cells In Date Range
Hello, we have a stop light spreadsheet for status with several different projects. In each row there are conditional format status green - good, yellow - needs work, red - bad...based on data from another tab. There is an "overall status" column that pulls the worst color that exists for a given row. =IF(COUNTIF(F1:V1,"r")>0,"R",IF(COUNTIF(F1:V1,"y")>0,"Y",IF(COUNTIF(F1:V1,"g")>0,"G",""))) There is also a date for each row. Now we want to pull the worst color in the "overall status" column for a date range...and display that on a separate summary tab. For 4/1/09 thru 4/15/09 - pull the worst color from the "overall status" column from that date range.
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
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
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 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
Flexible Range Formula: Return The Minimum Value From A Column Range
I need a formula that will return the minimum value from a column range that flexs without having to manually go in an change row references. There is a blank row between each section of data in order to separate info. As an example:.... Need to formula to flex to include rows 5 thru 7 in the first section but expand to include rows 9 thru 13 in the second section. =MIN(A5.A7) works but how do I get next section to flex to =MIN(A9.A13) without manually changing the cell references? There is always a blank row in between the sections to separate.
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
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
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 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
Highlight Minimum Cell In A Range
I have 4 cells and I want to highlight the lowest valued cell. At the moment I have the conditional formatting formula of =AND($C$5=MIN($C$5:$C$10),$C$5>0) However if C5 = 2 and C9 = 0 then C5 doesnt get highlighted as 0 is lower than 2. However I do want it to be highlighted. How can I ignore the zeros in the range im testing?
View Replies!
View Related
Minimum Value From Specified Column Of Range Matrix
I have a 10x10 array that represents different cities that a travelling saleperson can travel to. Rows are cities designated as i values, columns are the same cities and represented by j values. I need to use a For, Next loop to determine the shortest distance (lowest value) in a given column. The i (row) that contained the lowest value is the first city to be visted and a boolean is entered for that j=i column, showing that the city has been visited. When pulling the minimum values from the column I need to ignore 0 values where the distance is between a city and itself. I'm having trouble coming up with a loop that takes identifies the i row with the lowest value that also ignores previously visited cities and takes the boolean into account. Maybe my Excel spreadhseet will clear up what I'm trying to do, The distances were generated using RANDBETWEEN(1,100).
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
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 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
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
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
Conditional Column Count
I have this big sheet, as u can see the device name in separate column and the information related to this device below it. i need to count the number of columns that contains = Fa or G only , also i need to count the number of columns that AdminStatus= up AND OperStatus=up ...
View Replies!
View Related
Count Conditional Dates
I track dates of training for my fire department. Training is due every two years I want to know when the date is more than two years old and when it will be two years old in the next six months or less. I have conditional formatting that changes the color of a cell based on two conditions. Condition #1 (Yellow) the date is more than 2 years old, [=TODAY()-B2>730] Condition #2 (Green) the date will be more than 1 year old in less than half a year. [=B2+182-TODAY()<185]. Is it possible to count each occurrence those two conditions with a formula?
View Replies!
View Related
Formula To Count Conditional Formats
I'm trying to count cells affected by conditional formatting and I need help tidying up my efforts so far. The numeric values in my table are formatted if they exceed values in corresponding columns on other tables, indexed via Hack #75. The logic for each cell runs someting like this: if cell is numeric if column in (E, M, X, Y) if (value <c1 or > c2) highlight in blue end else ' columns not in (E, M, X, Y) if (value > c1 or < c2) highlight in red end end end My attempt to put together a formula for each cell appears to work, it looks like this for cell B3: '=AND(ISNUMBER($B3), OR( AND(.....................
View Replies!
View Related
Count Of Conditional Format Cells
I have a large spreadsheet which I require people to regularly complete. Due to the complexity of my business there are 180 columns, each of which has a variety of rules towards its completion. I have used conditional formatting to check as many of the rules as possible (e.g. if somebody enters that delivery is required then the columns for delivery address cannot be blank). Each column has the same conditional formatting but can have 1,2 or 3 rules. Min is always 1. Most columns are fairly unique conditional rules. There is a variable number of rows each time it is filled out (i.e. people add rows as required) but they copy and paste an existing row so conditional formatting is copied as well. All conditional formatting rules use the "formula" setting and nearly all use many "AND", "OR" statements to make all the necessary checks. If a cell is found to have failed the validation checks (e.g. is blank when should have an entry, has an entry when it should be blank) then it highlights the cell in red. I now want to have a count of the number of "red" cells to show me quickly if it has been completed "correctly" or not. I have written the vba code to check all the cells but cannot find out how to record if the conditional formatting is being used or not. "colorindex" returns the original colour of the cell not the conditional formatting.
View Replies!
View Related
Count By Conditional Formatting Condition
I work as a teacher and my role is the analysis of data, something I can do fairly easily using my Excel knowledge but I want to take it a step further (maybe this isn't the best was and another suggestion would be great. The spreadsheets I use have 400 students in columns A (first name) and B (last name), in columns C to AC I have the students targets for the 26 subjects/courses we offer, a student would only study between 10 and 14 of these so in a row there would be blanks. In cells AE to BE I have the students current grades (those which show current situation/progress). The first student would be in ROW 2. I want to show whether a student is below, equal to or exceeding their target and have done this using Conditional Formatting (3 separate conditions) using RED for below, White for Equal to and Green for Exceeding. I now want to count how many of each colour there are in each row to quickly work out how many of the subjects the students are falling behind in so we can focus our efforts on these.
View Replies!
View Related
Count Conditional Formatting Results
i am trying to count some cells in a range that are coloured either Red, Amber or Green. the cell colours are defined using conditional formatting. each cell has different criteria for the conditional formatting, but the outcome will always be one of Red, Amber or Green. i have used the code that is in the FAQ section (the '=ColorFunction' code) and this works fine if the cells are coloured manually. as soon as conditional formatting applies the colour, the formula seems to stop working (even after manually recalculating the worksheet). i have attached my spreadsheet - i am trying to solve the formulae in cells B21:B23.
View Replies!
View Related
Conditional Count Excluding Hidden Columns
I was wondering if was possible to create a conditional calculation excluding hidden columns. For example in C4:HA4 the cells may have a number of values L, S, U etc. When a user access the spreadsheet certain columns a hidden based on comparing their PC user ID to an access list in the workbook. What I would like to be able to do would be to count the instances of L in C4:HA4 but exluding the hidden columns. I'd hoped I might be able to use SUBTOTAL in a similar way to Domenic came up with here: =SUMPRODUCT(SUBTOTAL(9,OFFSET(C2:C100,ROW(C2:C100)-ROW(C2),0,1)),--(C2:C100
View Replies!
View Related
How To Count Conditional Format Colour Cells
Below I have an example from A1 to V1 which have 3 conditional formatting conditional format 1. if value is 0 then colour red and font white conditional format 2. if value is from 1 to 10 then colour blue and font white conditional format 3. if values 11 to 25 colour green, font black ABCDEFGHIJKLMNOPQRSTUV11230507801011121314150170192002523 From the table above I want to count the cells which are in 3 different colours. Result: Red=6 cells Blue=7 cells Green=9 cells
View Replies!
View Related
Count Duplicates Conditional Formating Highlight
I am trying to teach myself something new, say I have a list of numbers in two rows and I want to count the numbers and have it tell me if there are duplicates and highlight the duplicates. I got the part about getting Excel to tell me if there are duplicates but I can not seem to figure out the conditional formating part. I uploaded a sheet.
View Replies!
View Related
Conditional Count On Specific Color Cells
I need to count the grey coloured cells in a column based on the value in a different column (Column I has the team that the person is in). I already have a code from a previous thread to count the coloured cells (below) but this doesn't work when using an array formula. I have also tried just selecting the cells I need it to count (the people within the specific team), but the formula doesn't work unless the range uses adjacent cells...
View Replies!
View Related
Conditional Count Of Specific Data In Table
My specification is: Calculate how many people within the UK live in a given area. Within that area what are the different age distributions (0-5 years old, 6-10 years, etc.) Then display the data twice - 1/ show each age range and how many people in the given area fall into each one. 2/ As before only report on people in the given area, and display age range, but also display them by ID number (col E in example file). Can someone help me build the pivot tables that performs my analysis? (I got it working with countifs but it was an inelegant longwinded kludge.) I've attached an example (.xls zipped up) which shows the data on one tab and my failed pivot table attempt on another.
View Replies!
View Related
Format Cells Based On Conditional Count
I have a drawing register that contains drawing reference numbers in column D. When a new revision of a drawing is issued it is added to the bottom of the list. What I would like to do is any previous revisions to be greyed out and struck through. The conditional formatting = COUNTIF($D$1:$D$100,D1)>1 works well but strike through all duplicates, I need the most up to date left without the format until it is superceeded
View Replies!
View Related
To Get Average Or Count Of Column With Conditional Statements For Date
The data: There are a number of columns, but we'll use the first 2 first. Column A has the dates from 1/1/2009 to 12/31/2009. Column B will has numbers, mainly 0-100. One cell will have a 'report date' which is just a date. The formula needed: I have a few cells labeled wk1, wk2, wk3. Week 1 will start with the 'report' date. wk1 I would like the Average of Column B IF the dates in Column A are within the 'wk'. wk1 = report date TO report date + 6. wk2 = report date + 7 TO report date + 13 and so on. My goal is to have the formulas all set so that when we move to another report date and the raw data is added, I can just change the 'report date' and everything (the wk numbers) will automatically update with the new raw data. The furthest I got was this, but it didn't work. =AVERAGE(IF('A3:A33>=VALUE(D3)
View Replies!
View Related
|
TRACKER
