Formula To Return Maximum Of Certain Data
Jul 4, 2012
Formula to return the max of certain data.
This is data for an electric car. T= Trip and C=charge.
Column F is the parking time between Trips (T).
For each "T" event in column E, I would like to return the MAX parking time available before the last "T" event for that day (date). I've highlight the last "T" events in red.
I have attached an example : forum2.xlsx
Column G is an example of the output that I would like to achieve.
Note: For the last "T" event of the day it can just return the actual parking time (shown in green).
View 3 Replies
ADVERTISEMENT
Nov 26, 2008
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 9 Replies
View Related
Mar 5, 2009
I require a custom data validation formula to limit cell value to maximum of 4 decimal places.
0.0001 ok
0.02 ok
0.3 ok
0.12345 fail
0.123456 fail etc
View 2 Replies
View Related
Nov 28, 2006
i have a matrix
index| a| b| c|
0001 |1| 2| 2.22| ( probably it will be much clear to see the attachment)
I need to lookup for the largest number in B2:D2 (in this case 2.22) and return the corresponding header ("C"). I though it would be simple with hlookup() and max() but i can't make it work.
View 2 Replies
View Related
Jan 8, 2008
If I have names in one column and amounts in the next column, how do I determine the maximum of the amounts and return the associated name from the other column?
View 2 Replies
View Related
May 8, 2009
What I'm trying to do is get the MAX function to return a value, but that value needs to come before the values reach zero. It's a cubic equation that basically starts, at the beginning of the data range, under 10 then hits zero and ends up near 30 at the end of the data range. The end data range max doesn't matter to me if the data range hits zero along the way. Is there any way to make the MAX function stop at a certain value, or is there another function that could accomplish what I'm trying to do?
View 5 Replies
View Related
Dec 9, 2009
I am currently using an array to return the MAX date value. This formula will enter 1/0/00 if the referenced cells are blank. I'd like the furmula to LEAVE THE CELL BLANK if the referenced cells are blank. {=MAX(IF(Bid_Circuits=$A45,Bid_Trim_Completed,""))}
View 5 Replies
View Related
Aug 13, 2008
i have a spreadsheet like the following
A B C
Country Revenue Month
1 UK 10 Jan
2 France 20 Jan
3 US 30 Jan
4 UK 25 Feb
5 US 35 Feb
6 France 5 Jan
and so on...
So where country = UK, France or US I want to retrieve the MAX revenue from all months and which month it was in. Eg UK max revenue was in Feb of 25. I am not sure how to apply the max formula with criteria. Is there any way to do this?
View 5 Replies
View Related
Mar 1, 2013
I am writing a VBA function that will search for a value in a column(say column B) in a table. This column can have duplicates. So my search needs to get all the records that match the value (including duplicates) and from these records i need to return only one value among records fetched that has maximum in another column in the same sheet(say column D).
Eg :
sno dept name marks r1 cs sush 55 r2 ece ram 68 r3 cs harish 77 r
So if i give "cs" value to search it must give me 77 as output. I tried to write my own Lookup function but isn't working and getting #value.
View 6 Replies
View Related
Nov 2, 2006
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 4 Replies
View Related
Sep 16, 2007
In attached example, I have 3 columns of fractions with a final row of sumtotals (taken from elsewhere). I test to see which column has the maximum value in each row and allocate the result with the column's heading. Sometimes, two or more columns have the same max value. In this scenario, I wish to select the column heading that has the largest sumtotal.
View 4 Replies
View Related
Oct 22, 2007
* I hav two columns B and N having some data. From column N i need to find the Maximum valued cell.
* And now i need the content of a cell in column B in the same row , which matches with maximum value.
View 2 Replies
View Related
Jan 23, 2008
How can I select specific values either side of a peak value?
In the attached file I have a peak value of 10 and would like to select both values of 5 so that I can then identify the frequency they occur at.
View 5 Replies
View Related
Mar 8, 2008
I have inventory in 9 cities. Cities names are in B2-J2. Line items are rows 3-100
In column K, I have the max formula for that row. What I need is, in column L, the name of the location city from row 2 where the max qty is located also color the max qty in the row.
View 2 Replies
View Related
Mar 13, 2008
I have two columns of numbers in column A & B. In column A I am trying to locate the maximum value and get the cell address of the number next to it in column B. I am currently using this formula to do this ..... =ADDRESS(MATCH(MAX(A1:A4),A1:A4),2,4)
It works ok, but if the maximum number is the first value in the column it is returning the last cell address in column B. I am not sure what I am doing wrong.
View 2 Replies
View Related
Jan 20, 2009
i'm looking to return the cell address of the max number in a given range?
View 3 Replies
View Related
Jul 15, 2009
I am a novice to VBA in excel, i have a table of more then 5000 rows. I would like to find a maximum value from each row and return the column title as "GL_Weld" or "Bend" or "Header" into another worksheet cell. i.e First Row max value = 0.011510 should return "Bend" into another worksheet
GL_WeldBendHeader
0.0001700.0115100.000500
0.0000000.0007800.000000
0.0002400.0110700.000480
0.0000000.0006600.000000
0.0000000.0037700.000000
0.0000000.0000100.000000
0.0002800.0108700.000480
0.0000000.0007100.000000
0.0002000.0112900.000490
0.0000000.0008400.000000
000.00002
00.000310.00001
000.00004
00.00030.00002
000.00001
00.000270.00003
000.00002
00.000260.00004
View 4 Replies
View Related
Dec 11, 2008
What I am try to do is look up the highest (maximum) date in a lookup up that has multiple results.
Please see attachment.
Column in question is 'U' (FinishDate)
Maximum date that I want to recover is in column 'P' (DATE_COM)
LOOKUP VALUE is found in column 'A' (QUOTE_NUM)
View 6 Replies
View Related
Mar 13, 2014
I have an Excel Worksheet with 80,000 lines on it. The Columns are arranged thus:
Col A
Col B
Col C
Col D
Col E
Col F
Code
Description
Cost
Rate
Mobile
0.13
1234
Australia Mobile
0.12
Not Mobile
[Code] ........
I am looking for an Excel Formula that will look up Australia & Mobile and then return the highest value of the range of cells it finds that meets that criteria and enters that value in the Rate column next to mobile (F3).
Then I just want a variant of the same formula that will look up Australia but exclude Mobile and then return the highest value of the range of cells it finds that meets that criteria and enters that value in the Rate column next to Not mobile (F4).
View 1 Replies
View Related
Jan 2, 2010
I have a got a table ( 10x30) with random numerical values.
How can I return a column number with maximum value?
Values will be changed periodically so the maximum number could be in different column each time
Example:
A-B-C-D-E-F-G
1-2-3-4-5-6-7
2-6-8-18-1-1-8
2-5-6-9-12-1-7
The results will be 4.
View 9 Replies
View Related
Nov 18, 2007
What is wrong with this formula
=MAX(ADDRESS(VALUE(D2),1),1 & ":" & ADDRESS(VALUE(D1),1))
when D2 is 2, D1 is 6 and
=ADDRESS(VALUE(D2),1),1 returns $A$2
View 9 Replies
View Related
Dec 6, 2012
I want to search a data set and return the maximum value based on 3 criteria using (array) formulas.
Say the data set contained the following:
Row 1: column headings (date, name, data 1, data 2, data 3, etc)
Column A: dates
Column B: names
Columns C through G: data
I want to find the maximum value for a given name, in a certain month, and a nominated data column (e.g. data 2). For example:
Month = June
Name = Geoff
Data column = Data 2
Max value = ????
All of the data in the data columns is numeric.
View 4 Replies
View Related
Jan 22, 2008
I am looking to select two values from a range based upon their relationship to the maximum value. In this specific case I am trying to calculate the Quality factor based upon the frequency response of a transducer. So I need to identify the Maximum impedance and its corresponding frequency. I also need to identify the 3db points either side of the maximum impedance.
The 3db points are found at 2/3rds of the maximum value of the impedance (Impedance Mag in attached spreadsheet). Calculating the first 3db point isn't overly difficult as I just calculate the value for the 3db point and use the MATCH function to find the closest Impedance value and then work out the corresponding frequency from that. I am stumped as to how to calculate the second 3db point (above the Max impedance value). I have attached a spreadsheet with frequency information in column A, the other columns are used to derive the values found in columns M (Impedance Mag) and N.
View 5 Replies
View Related
Sep 16, 2009
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 4 Replies
View Related
Oct 23, 2008
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 4 Replies
View Related
Jan 20, 2010
I am having an issue getting MAX to pull the largest number from a range of numbers calculate by formulas. The code I am using to figure these numbers is as follows:
View 4 Replies
View Related
Oct 9, 2008
Look in Column E and find the MAX value. Once you find the MAX value, (let's say E27) display the date that's in C27. I bet this is really easy but I've been screwing around with it for over a half hour and can't get the correct result.
View 2 Replies
View Related
May 15, 2009
I have two columns of data. The first column is the 'type' and the second column is the 'value'. I need to find the maximum 'value' for each individual 'type'
The 'types' are not necessarily next to each other and the data cannot be sorted to do so.
Example:
type value
A 15
B 6
A 21
C 7
B 13
I need to be able to say the MAX for 'A' is 21, the MAX for 'B' is 13 and the MAX for 'C' is 7.
View 2 Replies
View Related
Dec 1, 2008
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 2 Replies
View Related
Jul 13, 2009
I have the following formula C= day in DD/MM/YYYY and D = Time in HH:MM format and BJ5 is the date
View 3 Replies
View Related
