# A Macro To Finding Maximum Value

May 16, 2009
i have a table same this in 5000 rows and 10 columns that are years from 1998 to 2007. ISO2 is two digit names of countries.

ISO2 D1998 D1999 D2000 D2001 D2002 D2003 D2004 D2005 D2006 D2007

CL 135 147 180 192 48 629 494 137 135 359

CL 132 145 177 190 45 623 491 134 132 356

CL 129 141 174 186 42 617 488 131 129 353

EC 126 139 171 184 39 611 485 128 126 350

EC 123 135 168 180 36 605 482 125 123 347

EC 120 133 165 178 33 599 479 122 120 344

KH 117 129 162 174 30 593 476 119 117 341

KH 114 127 159 172 27 587 473 116 114 338

KH 111 123 156 168 24 581 470 113 111 335

PH 108 121 153 166 21 575 467 110 108 332

PH 105 117 150 162 18 569 464 107 105 329

PH 102 115 147 160 15 563 461 104 102 326

PH 99 111 144 156 12 557 458 101 99 323

VE 96 109 141 154 9 551 455 98 96 320

i want a macro to advance filter column A and make a unique list of it and then find the maximum value of each country in column A in 10 years. for example final table for country "CL" must be same this

ISO2 D1998 D1999 D2000 D2001 D2002 D2003 D2004 D2005 D2006 D2007

CL 135 147 180 192 629 629 494 137 135 359

View 3 Replies
ADVERTISEMENT
Jan 26, 2014

I need a formula in order to find each time the maximum value.

View 3 Replies
View Related
Nov 12, 2008

Very simple problem. I need the second highest value in an array. Max gives me highest. Min gives me lowest. I need a kind of a MAX2 function.

View 3 Replies
View Related
Jan 26, 2010

I have to sets of data, each in a sheet with the first column as identical for both sheets. Sheet2 contains two series of 6 rows, each for a specific first column (""B Code").

Now I want to find the values of "TCT-C" column in sheet1 (for each range of 6 rows that the "B Code" is match) which corresponds to the row number that "TCT" in sheet2 is maximum (again for 6 rows). The point is, I need to shift to another series of 6 rows in sheet2 once the "B Code" does not match.

View 9 Replies
View Related
Feb 2, 2006

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 14 Replies
View Related
Feb 12, 2007

I have a range of overtime data. I want to list the maximum occurrence of first 10 overtime data ( Like 3200,2950.2300....etc) Thereafter from the results obtained I need to know the cell address of the employee corresponding to the results obtained, i.e XYZ, ABC.....

A B

Employee Name OT Amount

1. XYZ 3200.00

23. ABC 2950.00

35. WER 2300.00

View 10 Replies
View Related
Dec 11, 2008

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 3 Replies
View Related
May 7, 2005

Problem:

Range A2:C10 contains the login and logout times of various ID's.

Each ID could log in and out a number of times a day.

How could we find the first time a specific ID logged in and the last time that same ID logged out?

Solution:

For each of the ID's in range A2:A10, enter two Array Formulas.

To find the first login time (Column B) enter the following formula:

{=1/MAX((A14=$A$2:$A$10)*($B$2:$B$10

To find the last logout time (Column C) enter the following formula:

{=MAX(($A$2:$A$10=A14)*($C$2:$C$10))}

ID______Login Time______Logout Time

1 ______02:40___________03:10

2 ______00:15___________03:20

1 ______06:20___________09:30

3 ______09:14___________11:05

4 ______11:00___________19:30

2 ______04:05___________06:55

3 ______12:08___________17:17

1 ______10:00___________16:20

2 ______08:12___________12:33

ID______First Login Time______Last Logout Time

1 ______2:40__________________16:20

2 ______0:15__________________12:33

3 ______9:14__________________17:17

4 ______11:00_________________19:30

View 9 Replies
View Related
Aug 6, 2013

I am now working on an excel spreadsheet which will automatically update every day so that one row will be added on the top of the sheet everyday.i.e.

A1 Date Price

A2 (New row added,date) (New row added,price)

A3 16/1/2013 5.5

A4 15/1/2013 5.6

A5 14/1/2013 5.45

... ...

Assuming that I am looking for maximum value of the column "Price", since max() cannot be applied to variable range, is there other method I could useto look for the most updated maximum value of column "price"?

View 9 Replies
View Related
Jul 6, 2009

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 9 Replies
View Related
Mar 21, 2014

I am writing a formula for finding a maximum out of three average values. The cells that will be populated with data are A1 to A5; B1 to B5; C1 to C5. The final cell where I want to write the formula needs to show the maximum of the three averages (average of data in each row).

The difficulty is that sometimes only the A and B rows are populated (and sometime only A row is populated) and if B and/or C are blank, the formula fails and shows "#DIV/0!".

How can I write so that if C is blank, then evaluate only A and B data. And If both B and C are blank, then just display the average of row A.

View 3 Replies
View Related
Nov 7, 2008

This should be simple to do but I can't figure it out. I have a database that lists operating room numbers in one column and the length of the surgeries performed in those rooms in another column.

I need a formula that will give me the longest OR time for a given room. For example the room numbers are in column A and the OR times are in Column B. I've tried something like

View 6 Replies
View Related
Jun 15, 2009

I’m trying to figure out how to show the highest occurrence of text in 10 cells which are not next to each other. Some of the cells will also be blank And the other thing is if there is a draw e.g. 5 2Z and 5 1Z I'd like it to show that, either with a word e.g. "DRAW" or anything really other than just putting whichever of the 2 "notes" in it feels like

Attached is a spreadsheet (all other data deleted) that shows what I am wanting to do – I have typed the results I am after directly in to the cells in columns BE & BF – BE will need some kind of countif, but when I try that is says I have too many arguments! I have to leave the columns in between blank in this case as they have other info in them, which is really annoying as if those 10 cells were contiguous then the formula

View 3 Replies
View Related
May 21, 2009

i have the volume of a reservoir in column A for lets say, 2 years, with monthly data (so 24 numbers in my list)

I also have some variables that feed into this list such as rainfall (fixed), and population (i.e water use, which i can manually change)

if i increase the population too much, the volume of the reservoir will at certain times of the year, fall into negative digits.

what i'm looking for, is a cell which works out the maximum value (in whole numbers) for my population, without any of the numbers in the reservoir volume list falling below zero.

View 9 Replies
View Related
Nov 14, 2012

i have product group,product name and the statistics. I'll use an example of students with score. I have these set of data:

A B C D

1 Student Name Score Sum

2 Student1 Anna 48 80=Sumif(A:A,A2,C:C)

3 Student1 Anna 32 80

4 Student2 Tom 30 80

5 Student2 Tom 30 80

6 Student2 Tom 20 80

7 Student3 June 55 60

8 Student3 June 1 60

9 Student3 June 4 60

Now we have 2 students with tied highest scores with 80 Scores. Naturally i want the rank to be as follow: Top1 Anna since she has the highest score "48", and Top2 Tom, and Top3 June.

The problem is, the score data can vary and Top3 can probably have the highest score and he still didnt make the highest score collectively. And there will also going to be other situations as well because im working on a very large data set, and not these 3 students.

the max score is tied, and since im making another column with sum scores, the data is going to be redundant, and hopefully theres a formula to ignore this.

View 2 Replies
View Related
Jul 12, 2008

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 9 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
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
Aug 29, 2007

Anyone know the maximum lenght/size limitations for a macro in excel?

hit this limitation a couple times, and it serves to be quite a annoynace... end up having to link them together with a Application.Run command.

Would be good information to have if anyone knows...

my guess is 2000 lines, but havnt tested, and am not sure if it is limited to lines necessarily... could be size too,

View 9 Replies
View Related
Jul 15, 2009

I would like to run a macro loop to find Maximum value out of these three column and return text into a cell as "maximum value found = A" or B ....

View 12 Replies
View Related
Feb 5, 2009

I've an excelsheet with quite advanced formulas that area really nested and using quite a bit of iteration.

What I would like to do is finding the maximum of

A while Amin<A<Amax

By changing B

There is a Variable C than should be constant unless it becomes larger than Cmax ( Cmax dependas on A,B ,C, D and all kinds of things)

If C is > than Cmax there is a variable D that can be changed has to be less or equal to Dmax

So the question is how to find the maximum of A while fulfilling all these variables?

What I've come up with so far is:

View 9 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
May 4, 2008

I'm writing a macro that will pull a set of dates from a worksheet and put them into an array. Then I want to find the max date in the array (most likely using some sort of sorting method).

The code below is how I'm putting the dates into an array. I'm not sure if using the date data type is best. I can't tell how it will sort the information. Is there an easy way to find the maximum of an array of dates?

Dim Date_Array() As Date

If Sheets("Sheet1"). Range("N" & i) = "Date" Then

j = j + 1

Redim Preserve Date_Array(j)

View 5 Replies
View Related
Mar 24, 2014

Daily At the end of day We receive sales files from our different teams, these files are received at different times due to difference in working shifts. I have collated last one month data and from this data I want to know the earliest and maximum time and date on which we received the files in last month, so that i can fix the receiving time for all regions.

View 5 Replies
View Related
Jun 6, 2008

I have the following macro that is supposed to find postcodes but it's not working correctly. I don't get an error message it just doesn't find any postcodes.

The postcodes are located on a sheet called "Postcodes" in A2-A2736.

If it finds the postcode it is supposed to return the contents of column B, E and F in a message box saying "B is your nearest branch. This is F miles away which should take E minutes to drive." B is the name of the branch, E is the drive time from the original postcode and F is the drive distance.

code[]

Sub FindBranch()

Dim MyPC As String, cl As Range, found As Boolean

found = False

how to display my code correctly.

View 9 Replies
View Related
Sep 16, 2008

I have a row of cells with names. Alex, John, Martha, Jim, etc.

Each person has a list of data under them. I have a master column of data. Depending who is on duty today, I want the macro to find the person and then copy paste special the master column over the person column.

Thus, in the macro code where it says

Find(What:="John",

I want it to refer to a specific cell. I.e. what:=$E$4.

Then it must go to the cell it found and copy paste the info in.

View 14 Replies
View Related
Feb 12, 2014

provide me a macro to give a particular message or Cell Name when the cell is blank. I have to check around Eight columns and the starting row is A10. Moreover the sheet can have different numbers of row at different time like some time only 50 persons data will be there and some times 60 or 85 like this but the maximum will be 300. So if the 50 persons data is there than it should not warn about row 51 onwards.

View 1 Replies
View Related
Oct 27, 2008

I have two spreadsheets, spreadsheet A and B. In each spreadsheet there is a column labeled 'event' and a number in that column. I need to match the event number in spreadsheet A with the same number in spreadsheet B. In spreadsheet B in the same row as the event number is another value that I need copied back to spreadsheet A.

Manually I would have to copy the event number from spreadsheet A and use 'find' in spreadsheet B. Then check in the same row as the event number in spreadsheet B for a specific number, copy that, head back to spreadsheet A and paste it next to the event number.

I've tried to create a macro to do this but when using 'find' to match the event number in spreadsheet B it keeps on using the first event number I used instead of moving down a row onto the next number. I can upload the spreadsheet if needed.

View 2 Replies
View Related
Jun 1, 2009

I'm working on a project in which i had to calculate the average of particular field and that also with a macro in this application i had done that that's working supperb but i'm coming accross a problem according to which the range which i had to take average dosen't contains all integers

eg if range is A1:A10

then data is like

79

80

98

TBA

98

TBA

TBA

N/A

N/A

N/A

now ave for this range can't be calculated directly as many values are strings

what i need is

using a avg function on this range where TBA(To Be Anounced) is to be treated as 0 and N/A(Not Applicable) as null value

here's da avg dunction which i had used in macro

View 6 Replies
View Related