# MAX function question

#### Hijrofoyle

##### New Member
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 can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.

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?

you could try

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

This will tell you the row number

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)

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.

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.

hi Hijrofoyle.

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

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

Regards Sir Vili.

On 2002-10-09 05:19, Sir Vili wrote:
hi Hijrofoyle.

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

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?

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?

Replies
12
Views
359
Replies
13
Views
401
Replies
4
Views
184
Replies
5
Views
258
Replies
4
Views
268

1,219,832
Messages
6,150,487
Members
450,968
Latest member
kubet6886

### 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.

### Which adblocker are you using?

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

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