find location max value in column

I need to find the cell location of the max value in a column. If 
paste the following [from =(cell onwards] into a cell I get the righ
result. Having a hgard time getting the right VBA code to make thi
work in an unattended macro. Probably some bonehead mistake on my part
but I cannot find it. Suggestions?

GOAL is to make this formula work.

Range("z2").Formula 
"=(CELL(e1,OFFSET(e1,MATCH(MAX(e1:e100),e1:100,0)-1,0)))"

TIA,

Ro

--
rroac
-----------------------------------------------------------------------
rroach's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2109
View this thread: http://www.excelforum.com/showthread.php?threadid=38690

0
7/13/2005 6:35:35 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
373 Views

Similar Articles

[PageSpeed] 37

Since you're only looking at column E, how about:

Range("z2").Formula = "=""E""&MATCH(MAX(E1:E100),E1:E100,0)"

rroach wrote:
> 
> I need to find the cell location of the max value in a column. If I
> paste the following [from =(cell onwards] into a cell I get the right
> result. Having a hgard time getting the right VBA code to make this
> work in an unattended macro. Probably some bonehead mistake on my part,
> but I cannot find it. Suggestions?
> 
> GOAL is to make this formula work.
> 
> Range("z2").Formula =
> "=(CELL(e1,OFFSET(e1,MATCH(MAX(e1:e100),e1:100,0)-1,0)))"
> 
> TIA,
> 
> Rob
> 
> --
> rroach
> ------------------------------------------------------------------------
> rroach's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21093
> View this thread: http://www.excelforum.com/showthread.php?threadid=386908

-- 

Dave Peterson
0
petersod (12005)
7/13/2005 8:29:18 PM
rroach Wrote: 
> I need to find the cell location of the max value in a column. If I
> paste the following [from =(cell onwards] into a cell I get the right
> result. Having a hgard time getting the right VBA code to make this
> work in an unattended macro. Probably some bonehead mistake on my part,
> but I cannot find it. Suggestions?
> 
> GOAL is to make this formula work.
> 
> Range("z2").Formula =
> "=(CELL(e1,OFFSET(e1,MATCH(MAX(e1:e100),e1:100,0)-1,0)))"
> 
> TIA,
> 
> Rob

There are more than a couple  of ways/formulas *"to find the cell
location of the max value in a column."*

I have a similar situation where I needed to determine what the maximum
value was in a column and all I needed was an Excel formula (with
conditional formatting). Applying this  formula to your situation
(Cells E1:E100), you can do this:

1. Highlight E1:E100
2. Go to Format/Conditional Formatting
3. Choose "Formula Is" & "Equal To"
4. Type "=E1=max($E$1:$E$100)"  -- without the quotes
5. Click "Format" and then choose a color
6. Click OK
7. Click OK

This is what the conditionally formatted formula will do. The maximum
number in Cells E1:E100 will be shown in color (the color of which you
chose in Step 5 above) -- which is the cell location you are looking
for.

Hope this will help you.

Regards.


-- 
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11019
View this thread: http://www.excelforum.com/showthread.php?threadid=386908

0
7/13/2005 8:31:47 PM
Dave--works AOK!

Thanks,

Ro

--
rroac
-----------------------------------------------------------------------
rroach's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2109
View this thread: http://www.excelforum.com/showthread.php?threadid=38690

0
7/13/2005 9:27:46 PM
Reply:

Similar Artilces:

Reports - Customization - Add new Columns
I have a few questions regarding customizing the active reports. For example, in detailed sales report, we want to add columns like Average Selling Price (ASP), Average Units Sold/Transaction etc. How do we go about it? What needs to be done? Any documentation available? Regards. ...

If cell value is greater than another cell value, clear contents.
Okay, hopefully this is the last question today! I've found the below formula in one of the posts here, but my brain has completely failed on me and I'm not sure how to adapt it to my requirements. I have a value which is entered by the user after a prompt, that populates in cell G4 in a sheet called 'Lookup'. For arguments sake, the current value is 30/11/09. I have another worksheet, 'Data', which has a column populated with dates (say column B). I'd like a macro that looks in column B on the data sheet, and clears out any dates which are gr...

Adding columns #4
I have a spreadsheet with values in column E an I want to add them to column F with the first f2=e2.So as I add a value to column E the value in F changes to sum all with the blank cell showing 0. ie Column E2 Column F2 $1000 $1000 $500 $1500 $0 Okay, so in F3, type: =if(isblank(E3),"",F2+E3) ******************* ~Anne Troy www.OfficeArticles.com "bbc1" <bbc1@discussions.microsoft.com> wrote in message news:9664CDDF-CC23-400A-AFCE-A8BA2974297C@microsoft.com... > I have a spreadsheet with values in c...

Combo Box default value
I am trying to set a default value for a combo box that is a static list with three items in it. Everything I have found googling has related to setting the default value in the controls properties using this format: =[controlname].ItemData(indexnumber) Using this suggestion hasn't worked so far and I am sure it is a simple solution. Thanks. Sorry, it is on a form. I want the default value of the combo box on the form to be the first item in the list. Thanks "Ofer Cohen" <OferCohen@discussions.microsoft.com> wrote in message news:EEEFDE47-409A-4D08-B426-0374F...

Unmerging and populating merged cells by column
This group is the best thing since sliced bread. The people that help out here are the best, thank you! With help from this group I've been able to copy and paste data across multiple worksheets into a single sheet. This is huge progress for me, and I now have one hurdle left. Some of the cells in my combined data worksheet are merged. However, my users are going to want to create pivot tables from the data, and the merged cells affect the results. So what I *think* I want to do (and someone may have a better idea) is to: 1. Go column by column through the worksheet. 2. As it goes down...

Numbering for column
Hi, Good Day! I seldom used microsoft excel but sometimes i do used it for some simple calculation. When I just opened an excel spreadsheet, the numbering of column is in number format(1, 2, 3...) and not in alphabet format(A, B, C...) May I know how can i reset it back to alphabet? Thanks in advanced. rgds, Phoebe Tools|Options|General|Uncheck R1C1 Reference Style (this is picked up from the first workbook you open in that session. So make sure you save your workbook after the change--and make a note of how you fixed it. It can happen again if the first workbook opened has R1C1 refe...

Insert spaces between pasted values #2
Hi! Is there a way to insert spaces between values that are pasted? For example, i have in rows 1,2,3 values a,b,c and i want to paste them into another list in rows 1,3,5 respectively (with a single-row space between pasted values). Thanx! M I presume you have more than just 3 rows. A general approach would be to use a helper column next to your data and to fill a simple sequence down that column for as much data as you have. Then copy all the numbers that make up that sequence and paste them immediately below the sequence in the same column, so that if you have 200 numbers for example the...

converting text values to number & decimal values..
I have any array which read a csv file and I am using split and populate these fields into my table.. temp= 5245,test1,23.45 temp1=3456,test1,23.45 Now I want to convert my text fields to number and decimels respectively. I tried the following functions but this works fine if they are number and fails it was text value. TESTID = CInt(sampleid) 'CInt(myarray(1))Convert to number CT_num = CDec(ct_val) -- Message posted via http://www.accessmonster.com Hi, Test to see if they are numeric first. If IsNumeric(sampleid) Then TESTID = CInt(sampleid) Else ...

How do I change the values in loan amortization
I am wanting to use the loan amortization spread sheet, and was wondering how you change the values as I want to change Loan period in Years to calculate Loan period in Months?? I have tried several things but not having a great knowledge on using Excel im failing miserably! PLEASE HELP!!! :O) Thank you! a combination of dividing by 12 and multiplying by 12. play with it -- Don Guillett SalesAid Software dguillett1@austin.rr.com "ROXYAWORLD" <ROXYAWORLD@discussions.microsoft.com> wrote in message news:46CED19F-2119-43B7-8BC1-57710F6E008D@microsoft.com... >I am wan...

In Excel how can I assign values to a Yes or No question with IF
I am building a spreadsheeet, where If a cell has a "Yes" in it is is work ..75 and a "no" equals 0. I know a IF statement will help, but I don't know how. I also want Excel to caculate from an inputted date how many moths ago it was. Can anyone help? Craig =IF(A2="Yes",.75,0) is all that you need -- HTH Bob Phillips "craigscoop" <craigscoop@discussions.microsoft.com> wrote in message news:72842B75-8485-497C-8AC6-01348498D73B@microsoft.com... > I am building a spreadsheeet, where If a cell has a "Yes" in it is is w...

how to automate min and max on axis based on calculation
While automating some chart making with code, I am trying to automate the span of time reflected on the x-axis. Auto doesn't work because I need the beginning to be based on an October of a given year. Similarly I have requirements for the max. The appropriate min and max are calculated elsewhere, but I am not sure how to code it to accept the value from a cell. Any help would be much appreciated. TIA This has to be done programmatically. For a ready-made solution see AutoChart Manager http://www.tushar-mehta.com/excel/software/autochart/index.html -- Regards, Tushar Mehta www...

Multiplying A Whole Column By 2 ??? Help
Hello, I recieve product stock list from my supplier in excell. 2 columns, A = products , B = prices I need a formula that will multiply all prices in column B by 2 so I can then easily forward stock list to my customers without revealing original values in column b. I have tried all the basic first thought ideas and none have worked. please provide help. thank you. joe@californiacichlids.com -- joesther37 ------------------------------------------------------------------------ joesther37's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30031 View this threa...

running total column help
Hi All, rule: T-SQL prior to SQL2005 Given this: row, amt, run$ ---------------- 1,$15,NULL 2,$0,NULL 3,$10,NULL 4,$15,NULL Transform to this: row, amt, run$ ---------------- 1,15,$40 2,$0,$25 3,$10,$25 4,$15,$15 Basically, is there a way to populate the run$ col with a running total Thanks, rodchar Try: update m set [run$] = (select sum (m2.amt) from MyTable m2 where m2.[row] >= m.[row]) from MyTable m -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server M...

Finding Server-Side Rules
I want to generate a report of all server-side rules, or at least all mailboxes containing server-side rules, on an Exchange 5.5 server in preparation for a migration to 2003. So far I have come up blank. Is there any tool out there to gather this kind of information short of writing a program using CDO? "=?Utf-8?B?V29sZg==?=" <anonymous@discussions.microsoft.com> wrote in news:3F616242-8854-47C2-A8C8-063D2045519C@microsoft.com: > I want to generate a report of all server-side rules, or at least all > mailboxes containing server-side rules, on an Exchange 5.5 server ...

Is there a maximum number of values entered into a pick list?
For our project we need to add a very large amount of values into a pick list. 1839 values to be precise. When and existing entity that contains the pick list is opened we get an error. The event view contains the following error: MSSQLSERVER Error: 17805, Severity: 20, State: 3 Invalid buffer received from client. The strange thing is that when we create a new record we do not receive and error. We receive the error when attempting to open up a saved record. Has anyone seen this issue? ...

Perspective setting won't save in a 3-D column chart
I have a 3-D column chart that has the perspective setting as 20, the elevation as 24 degrees and the rotation as 198 degrees. I change the settings, close the dialog box and click save. Yet the perspective setting resets itself to 15 every time I open the file. I am using Excel 2007. Any suggestions? Thanks. Hi Kathi, I have been able to duplicate your problem, I suggest this is a bug, although there might be some reason for this behavior, I can't think of any. It's probably not worth it but I suppose you could attach a macro to the Open_Workbook event and reset it to...

change column inside DB
I have for example: [Table(Name="dbo.somedatabase")] public class License { [Column(IsPrimaryKey = true)] public int DeviceID { get; set; } [XmlIgnore] public string Name { get; set; } } Inside class License I keep data. I'm using Linq to inserting this data to database (SQL Server 2008). But I need to have mechanism like this: When I change name or type of property inside this class I need to change database table column before I insert data there. How to do this? ...

Finding data for a chart in word
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3325082853_2433946 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit After inserting a chart in a word document I cannot seem to get back to the excel worksheet update data on the chart. Advice please. Thank you. Joe --B_3325082853_2433946 Content-type: text/html; charset="US-ASCII" Content-transfer-encoding: quoted-printable <HTML> <HEAD> <TITLE>Finding data for a chart in word<...

Column additions query
I have 2 columns of numbers. There are no 'zeros' in Col 1 - all are greater than zero. Some of the numbers in Col 2 are zero but most are greater than zero. I want to be able to add all the numbers in column #2 that are greater than zero and then divide that number by all the numbers in Column #1 that coincide only with those numbers in Col 2 that are greater than zero. In other words I do not want to include any numbers in the Col 1 addition that are on the same row as the 'zeros' in Col 2. regards PeterH You could use two SumIf's. Something similar to: =SUMIF(...

Setting length of all cells in a column
I have a column in a spreadsheet that holds bank account numbers. Unfortunately, although a bank account number has to be 8 characters long (e.g. 00012345), some of the leading zeros have been dropped, meaning that the cell > only contains, for example, 12345. Some fields only have one leading zero, so the field has been dropped to 7 characters long, whilst some have no leading zero so are already the required 8 characters long. There are 8000 lines on this spreadsheet so changing the lines manually is not feasible. Any idea how I could quickly change those fields that are not alr...

checking duplicate values on data entry
Dear all, In my table design,Because I need to accept No value , I can not use NO duplicate index for my field , but I shouldn't let duplicate values in it. to develop the need I check the value in the data entry form using "Find first" of the record set. On my own computer it is OK and no duplicate value can be entered. but users CAN add duplcate value.(mine is office xp but some use office2003). what should I check? and if there are better solutions please let me know. tnx in advance. The version of Access should make no difference. How are you using FindFirst a...

Compare values #3
Hi All, I'd really appreciate if someone from the group can please help me get through this question. I've 4 columns viz A, B, C & D and here's what I need to do: 1) I've to check values in column A against column C and values in column B against column D. If the values match then simply leave things as is. However if the values in column A are different then values in column C or values in column B are different then values in column D then output that record to a different sheet. Can someone please help me w/the programming so that I can get the desired outcome? Thank...

Append a column to a table
I have what i think is a simple task, but i am new to access and need some advice. Basically, i am trying to use a table as a report and need to update the table by adding a list of phone numbers as a new column. The table that has the phone numbers is alongside a list of provider numbers, while the table that I need to append also contains those same provider numbers (i.e., so the provider numbers should allow me to link the data in somehow). The table that I wish to append does have duplicate provider numbers, whereas the table with the phone numbers DOES have duplicates. What is the best ...

how can i save mail a folder column view?
i have to open each mail address folder (using multiple email addresses and sub folders) everytime i start outlook--how can i save a current folder view as the default with all folders opened? ...

Conditional formatting a column for even numbers
Hello all, could you please tell me how I would use conditiona formating for a column of data so that all the even numbers ar highlighted. Thanks for your help -- greg746 ----------------------------------------------------------------------- greg7468's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=903 View this thread: http://www.excelforum.com/showthread.php?threadid=26537 Greg select the column and use: Formula 1: =AND(ISNUMBER(A1),MOD(A1,2)=0) assuming it is column A you want to format. Regards Trevor "greg7468" <greg7468.1dfrmn@exce...