MAX function question

Hijrofoyle

New Member
Joined
Oct 7, 2002
Messages
35
when the MAX function returns a value from a column i.e. MAX(A1:A50) is there any way to determine what cell it's refering to? Where the cell with the max value is?
I'm using the max function and it keeps returning a max value of 3.440000000E+00 but that is not a value that occurs in any of my columns occuring to the find tool. Any help would be appreciated.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
Do a 'binary' search; split the range in half, do one, when you find the questionable value, split the range in half.

When you again find the questionable value, split the range in half and Max() again.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-10-09 04:47, Hijrofoyle wrote:
when the MAX function returns a value from a column i.e. MAX(A1:A50) is there any way to determine what cell it's refering to? Where the cell with the max value is?
I'm using the max function and it keeps returning a max value of 3.440000000E+00 but that is not a value that occurs in any of my columns occuring to the find tool. Any help would be appreciated.

What is the result of

=MATCH(9.99999999999999E+307,A:A)

and

=MATCH(MAX(A1:A150),A1:A150,0)

By the way, you just mention A1:A150 as the range whose max value you want determine and start talking about "that is not a value that occurs in any of my columns"! Many columns, thus not just A. What is up?
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
you could try

=MATCH(MAX(A1:A50),A1:A50,0)

This will tell you the row number
 

Hijrofoyle

New Member
Joined
Oct 7, 2002
Messages
35

ADVERTISEMENT

what i meant by 'any of my colums' is that i've done a single column search on a number of columns, and each one is returning 3.44000..... but none of the columns data are related (derived from different raw data, though each is trying to determine an angle of declination, so the formula's are the same)
 

Hijrofoyle

New Member
Joined
Oct 7, 2002
Messages
35
On 2002-10-09 04:54, stevebausch wrote:
Do a 'binary' search; split the range in half, do one, when you find the questionable value, split the range in half.

When you again find the questionable value, split the range in half and Max() again.

is there any way to automate this? I'm working with 30,000 rows of data here.
 

Hijrofoyle

New Member
Joined
Oct 7, 2002
Messages
35

ADVERTISEMENT

On 2002-10-09 04:55, lasw10 wrote:
you could try

=MATCH(MAX(A1:A50),A1:A50,0)

This will tell you the row number

Thanks for the advice but I tried that and the value that it returned for the row number(12003) wasn't the maximum value in the column.
 

Sir Vili

Board Regular
Joined
Jul 11, 2002
Messages
148
hi Hijrofoyle.

This tells You in which cell is max in area E1:E65000.

=ADDRESS(MAX(IF(E1:E65000=MAX(E1:E65000),ROW(E1:E65000))),COLUMN(E1:E65000),4)

This is Array Formula, so when ready with formula, use CTRL + Shift + Enter instead Enter.

Regards Sir Vili.
 

Hijrofoyle

New Member
Joined
Oct 7, 2002
Messages
35
On 2002-10-09 05:19, Sir Vili wrote:
hi Hijrofoyle.

This tells You in which cell is max in area E1:E65000.

=ADDRESS(MAX(IF(E1:E65000=MAX(E1:E65000),ROW(E1:E65000))),COLUMN(E1:E65000),4)
Thanks, extremely helpfull, but what is the signifigance of the 4 at the end of the formula? and am i correct in assuming that i can just substitute min for max to get the same result wit that function?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-10-09 05:12, Hijrofoyle wrote:
what i meant by 'any of my colums' is that i've done a single column search on a number of columns, and each one is returning 3.44000..... but none of the columns data are related (derived from different raw data, though each is trying to determine an angle of declination, so the formula's are the same)

What are the answers to other questions?
 

Forum statistics

Threads
1,143,709
Messages
5,720,391
Members
422,283
Latest member
Parvathy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top