Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Listbox Minimum & Maximum Range
I have the two buttons on userform "viewer" that try to push the listbox past it min and max range. resulting in a error. Could it be that if listbox is at min it set the button to hidden.

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
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
Minimum And Maximum Values Within Specific Date Range
I have a table of dates and values and would very much like to know a formula for returning the min and mean values for each column within a specified time (date) period.

Table is as follows

Date value(a) Value (b) ....
1/1/09 10 8
2/1/09 8 6
3/1/09 5 2

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
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
Minimum And Maximum Value In Sumproduct Formula
Is there a simple way to find the min and max value for my formula below

HTML =SUMPRODUCT((Data!$J$2:$J$10000="E700")*(Data!$M$2:$M$10000>'Rental Rates'!$A3)*(Data!$M$2:$M$10000<'Rental Rates'!$B3)*(Data!$M$2:$M$10000))

View Replies!   View Related
Finding The Minimum Value In A Column That Comes After The Maximum Value
Say I have 2 columns that in basic form look like this:

Column A Column B
Jan 1
Feb 0
Mar 7
Apr 4
May 15
Jun 2
Jul 5
Aug 4

First I want to look up the max value in this column. This is easy =max(b1:b8)
Then I want to know the minimum value that occurs after the maximum value. Thus the answer would be 2.

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
Count No Of Maximum / Minimum / Median
here is an attachment... i have found array fromulas for finding Maximum, Minimum & Median. Now i need to find the no of Maximum, Minimum & Median.

View Replies!   View Related
MAXIMUM/MINIMUM Formula With Time
I have the following formula C= day in DD/MM/YYYY and D = Time in HH:MM format and BJ5 is the date

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
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
Sum Minimum & Maximum From Each Row
data in 3 colums (A2:c100)

Would like to sum the maximun values in each row together.

is Max(A2:C2) + Max(A3:C3) + Max(A4:C4) ........

I assume whatever works with max I can change to min.

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
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
Idendify Cell With Minimum & Maximum Value
If I have a column full of data (call it 5000 data points) and use =min(a1:a5000), how do I find the cell this function is referencing without sifting through 5000 entries?

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
Minimum And Maximum Functions Pull The Highest Price
I have a worksheet that is made up of the following:

Column A : Date
Column B : Time
Column C : Price

I want to use these two functions, to pull the highest price:

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
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
Calculate Price Based On Minimum
Trying to create a Math formula that allows for Taking advantage of sale pricing.

In Excel the following:

Qty to Order (G3), Std Package(I3), Price per Unit(J3), Sale Price(K3), =

Extended Price(L3)

Example

2case X 40peices per case,1.00Reg Price,.90Sale Price = 72.00

Not quite sure how to handle the Sale price taking over regular pricing

View Replies!   View Related
Calculate Future Value Of Required Minimum Distributions For IRA
I've written the following code. It calculates the future value of required minimum distributions for traditional IRAs. When I enter the function in excel, it drops the last argument. I've tried changing the order of the arguments, and it always drops the last one. The arguments are : marginalTax (marginal tax rate), r (expected return on investment), t (time), begAge (beginning age), RDT (as range -- required distribution table; looks up the percentage required at each age), IRAValueat70 (calcualtes the value of the IRA at 70 years of age)

Function RMD(r As Double, marginalTax As Double, begAge As Integer, t As Integer, RDT As Range, IRAValueat70 As Long)
age = begAge
endAge = begAge + t
Dim holder As Long
Dim RMDnow As Long
Dim RMDall As Long
hat = 70
If endAge < 69 Then
Else
If begAge <= 69 Then
Do While hat < (endAge)
RMDnow = (IRAValue / (Application.VLookup(age, RDT, 2, False)))
RMDnow = (1 - marginalTax) * RMDnow...........................

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
Calculate Total Time With A Maximum Per Day
Date Start End Total
12/8 - 2:00 3:00 = 1
12/8 - 4:00 5:00 = 2
12/8 - 20:00 21:00 = 5
12/9 - 23:00 0:00 = 3
12/9 - 3:00 4:00 = 4

=SUMIF($A:$A,">="&TODAY()-1,D:D)/24-SUMIF($A:$A,">"&TODAY(),D:D)/24

In the example above, E2 calculates the totals between 12/8 and 12/9 giving me 15:00. What I want the formula to exract is the total from Col D the total from 12/9 to the previous 24hrs.

12/8 has 2 entries, and 12/9 has 3 entries. The total I want to extract in 24 hours, NOT 1 day, is 14. What formula can I use that will calculate the totals in 24 hrs.

View Replies!   View Related
Calculate Maximum Point On Curve From Xy Scatter Graph
I have put my values into a XY scatter graph but need to find the maximum point i.e when the gradient is zero. I am using visual basic to obtain my data and draw my graph.

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
Minimum Function In VBA: Find The Minimum Cumulative Cost In Week 0 Out Of The First Three
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.

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
Minimum In Range Greater Than Zero
I want to find the minimum time value within a range of cells, excluding 0:00. Currently, =MIN(BL5:CP5). returns 0:00 if it exists in any of the cells.

View Replies!   View Related
Find Minimum SUM If No Minimum Number In Row
I want in A1 to find minimum SUM if no minimum number in row.

Here is example attached: ...

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
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
Conditional Minimum With Non Contiguous Range & Hide If Zero
I have 3 columns L, N and P

I would like to return the MIN value(column R) between the columns
except where the MIN = zero then just leave the result as blank.

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
Maximum Text From Range
I am trying to get the maximum from a range of text cells. The data in the cells consist of

00001
00003
00014
00028
00007
etc.


the formula I am using is:
=MAX(VALUE(Interface!E2:E20))

the answer that is returned is 15 and it should be 28. Any ideas on how I can fix this?

View Replies!   View Related
Change Range Of Maximum Value
From A1:A500, I have data with different numerical values. I want to be able to use a corresponding column and rows (example: B1:B500) where I can enter formula where maximum values where be reported. This I would like to change with a simple change of a single cell value (example: C1). So, If C1 has a value of 10, it will be used from every b cell to create a maximum value that would use a range going back only 10 cells for A.

View Replies!   View Related
Lookup Value Above Maximum Value Of Another Range
How to lookup for greater than a given particular value in a list of data? Suppose the value to find is in cell C17 & the column to be looked for is F, in the attached sheet. Now the data range is from F8:L27. If the value in C17 is not present in Column L, then the value greater than the specifed value should be searched in Column L. I have tried using VLOOKUP, but cannot find for a range of values.

View Replies!   View Related
SUM MAXIMUM Or Index/Max: Count Number Based On The Maximum Time??
I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).

=SUM((Download!$H$2:$H$10=A4)*(Download!$D$2:$D$10=MAX(IF(Download!$H$2:$H$10=$A4,Download!$D$2:$D$1 0)))*Download!$I$2:$I$10)

Would a Index/Match/MAX function be more efficient?

View Replies!   View Related
Maximum Range For Sum() Function?
I am trying to sum 44,582 cells in a column, the values in all of them are either 0 or 1, but I always get a value of 0.

It works when I bring this right down to 1000 or 1500 cells but it can't seem to sum the entire range.

This is Excel 2004 for Mac OSX Version 11.3.5


View Replies!   View Related
Finding Maximum Number For A Range
selecting a range under a condition. I'd like to write a macro to find the maximum value for a certain ID. The number of ID rows (left column) is unknown so can't say range("A1:A5"). Then have the ID and max number shifted to another column. Maybe use 'rows.count' ?

Eg.

4009 5
4009 9
4009 8
4009 4
4005 7
4005 3
4005 9
3008 10
3008 11


View Replies!   View Related
Select From A Range If Maximum Exceeded
I hope the title makes sense. I have a spreadsheet that I'm trying to use to calculate volumes of liquid. For each liquid there is a minimum and maximum amount that can be included in a vial, and a checkbox to indicate if it should be included or not. What I'm having trouble with is some logic (other than a million if= statements) to determine the amount of each needed.

If only a few are selected, using the max value for each is fine as it is below the volume of the vial, so I want to use the max. But if enough are selected, the max for each would exceed the volume of the vial, and I want to pick a percentage of each component's range until we get a volume that will fit in the vial (ie, it figures out that 84% of the max for each will fit, but knows never to drop below the min).

View Replies!   View Related
Determine If Cell Maximum In Range
I have a spreadsheet where I need to check if one cell value is greater than 6 other cell values in the row, then have it enter 1 in another cell if so.

For example: C1=5 C2=2 C3=3 C4=2 C5=7 C6=0 C7=4

The largest value is in cell C5, so I would like C6 to show that it is by entering a 1 in there.

View Replies!   View Related
Find Maximum Value From A Pre-filtered Range
I am looking for a way of finding the maximum value in column H for each row in a pre- filtered range using VBA.

For example, the result of of filtermacro1 may return several records derived from a database of several hundred records. In the example below, the name of the person is in column C, followed by their H column value.

Audrey Perkins, .5
Marc Bloomberg, 2.0
Matt Phillips, .5
Paul Pachson, 2.0

However, the H column value may not be the maximum value for the rows above, as there are other records for Audrey, Marc, Matt & Paul in the database.

If the names are matched to the max value in column H for each record it should read

Audrey Perkins, 1.0
Marc Bloomberg, 3.5
Matt Phillips, .5
Paul Pachson, 6.5

How would I query only the names in a pre-filtered range and obtain the highest value in column H for each ? These values then need to be copied to H7 in a sheet called Detention Register.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved