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

Finding Each Time Maximum Value?

Jan 26, 2014

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

View 3 Replies View Related

Finding 2nd Maximum In An Array

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

Finding Equivalent Of Maximum Value In Another Sheet

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

Finding Maximum Value While Excluding Some Values

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

Finding The Cell Address Of Maximum Value

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

Finding The Minimum Value In A Column That Comes After The Maximum Value

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

Finding The Maximum Value Of All Those Meeting A Certain Criteria

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

Finding Maximum In A Variable Range?

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

Finding Maximum Number For A Range

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

Finding Maximum Value (but Exclude Blank Cells)

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

Finding The Maximum Value In A Column Based On A Condition

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

Finding The Maximum Occurrence Of Text Over Non-consecutive Cells

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

Finding The Maximum Number Before A List Turns Negative

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

Excel 2010 :: Finding Data With Tied Maximum Scores

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

Finding The Minimum Time And Maximum Time

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

SUM MAXIMUM Or Index/Max: Count Number Based On The Maximum Time??

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

Find MAXIMUM, Show Date When MAXIMUM Occurred

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

Conditional Maximum (find The Maximum 'value' For Each Individual 'type')

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

Maximum Length For A Macro

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

Macro Code To Find The Maximum Value

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

Advanced Loop In Macro Find Maximum Value

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

VBA Macro To Return Maximum Value In Text Form..

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

Maximum Date In Array Macro Code

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

Macro To Find Maximum And Minimum Time From The Data?

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

Look-up Macro Not Finding What It Should

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

Finding Cells In A Macro

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

Macro For Finding Blank Cells?

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

Macro For Matching Between Two Spreadsheets And Finding A Value

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

Finding Average Of A Range With Macro

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







Copyrights 2005-15 www.BigResource.com, All rights reserved