MAX function question

[Hijrofoyle]

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.

 

[stevebausch]

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]

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]

you could try

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

This will tell you the row number

 

[Hijrofoyle]

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]

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]

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]

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]

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]

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?