Insert Cell Name of a value that matches a MAX function

[Al Rios]

I have a spreadsheat..with a Max formula, the formula
works, but I am also trying to find the cell name of that
output. Example

Column Names:

A1 b1 b1 b1
Start date 1st date 2nd date 3rd date
2 May 04 3 May 04 6 May 05 20 May 04

Thet max function will find the 20 May 04 as the lasted
value in another spreadsheet I have.

I also want it to insert the cell name of the max value.
Such as 3rd date. Which we tell me the lasted date is 20
May 04 and it came from the column named ..3rd date.

Thanks for the help..

Al..

 

[Guest]

=MATCH(MAX(A11),A11)&" "&MAX(A11

This gives the position and date number, but the date is not formated

 

[Frank Kabel]

Hi
if your values are in row 2 and the column heading in row 1 try
=INDEX(A1:X1,1,MATCH(MAX(A2:X2),A2:X2,0))

 

[AL RIOS]

-----Original Message-----
=MATCH(MAX(A11),A11)&" "&MAX(A11)

This gives the position and date number, but the date is not formated
.
It does what you say, but I need the actual text. Can

you help..

Thanks again

 

[Al Rios]

This works great,:=), what I need now is when the values
are blank that it not insert values of NA.. I just want
the fields to be blank if nothing it there.

Thanks Again,

 

[Frank Kabel]

Hi
use
=IF(ISNA(MATCH(MAX(A2:X2),A2:X2,0)),"",INDEX(A1:X1,1,MATCH(MAX(A2:X2),A
2:X2,0)))

 

[Al Rios more help ...]

Thanks Frank,
I'm sorry to bother you again, I modified your formular
to match what I needed to connect to another spreadsheet
named Project and it works great, But I need the values
to be store as blank when values =INDEX(Project!
$E$1:$H$1,1$E$1:$H$1,1, or when MATCH(MAX(Project!E2:H2),
Project!E2:H2,0))

See formula below


=INDEX(Project!$E$1:$H$1,1,MATCH(MAX(Project!E2:H2),
Project!E2:H2,0))

Thanks again

 

[Al Rios]

(e-mail address removed)> Sent: 5/31/2004
4:41:18 AM




Thanks Frank,
I'm sorry to bother you again, I modified your formular
to match what I needed to connect to another spreadsheet
named Project and it works great, But I need the values
to be store as blank when values =INDEX(Project!
$E$1:$H$1,1$E$1:$H$1,1, or when MATCH(MAX(Project!E2:H2),
Project!E2:H2,0))

See formula below


=INDEX(Project!$E$1:$H$1,1,MATCH(MAX(Project!E2:H2),
Project!E2:H2,0))

Thanks again

 

[Frank Kabel]

Hi
see my other post. Use the following formula
=IF(ISNA(your_formula),"",your_formula)

 

[Al Rios]

I get an error. Sorry for the constant help...I'm trying
to help my son,

See Formula below

Thanks again

IF(ISNA(INDEX(Project!$E$1:$H$1,1,),"", MATCH(MAX(Project!
E1:H1), Project!E1:H1,0)))

 

[Frank Kabel]

Hi
try
=IF(ISNA(MATCH(MAX(Project!E2:H2),Project!E2:H2,0)),"",INDEX(Project!$E
$1:$H$1,1,MATCH(MAX(Project!E2:H2),
Project!E2:H2,0)))

 

[Al Rios]

Get an Error,

It states the "Formula You enter contains an Error"

This is what I have:

E1 thru H1 are text Values from the header
E2 thru H2 are date values..

Above may help..

Thanks very much..

 

[Al Rios]

Get an Error,

It states the "Formula You enter contains an Error"

This is what I have:

E1 thru H1 are text Values from the header
E2 thru H2 are date values..

Above may help..

Thanks very much..

 

[Frank Kabel]

Hi
the formula works for me. any chance you have a different separator or
a non-english Excel version?
Does Excel highlight a specific part of your formula?

 

[Domenic]

Hi Al,

I just tried the formula and it works fine. However, I did get the
error when I first copied/pasted it into my worksheet. I think hard
returns seemed to have been the problem, probably due to copying/pasting.

You'll find that if you type it in, you won't have a problem.

Hope this helps!

 

[Al Rios]

I sent you an email with attachment in Color. Hope this
helps.

 

[Al Rios]

Just typed it in and it works fantastic.

I tried to use the same concept for anther field but no
luck.

Can you help

=if(ISNA(Project!D10)),"".


You guys are great....

 

[Frank Kabel]

Hi
this works only if cell D10 conatins a #NA error. If you want to check
for any error try
=if(ISERROR(Project!D10)),"".

 

[A Rios]

Thanks for the help, bit it did not work
The function I use below displays a values of 0 (Zero)
when nothing is in that field.

=Project!C5 (if this field is blank it displays a zero).
I could use the tools option to not display zeros but if
we want to add other numerical fields and display zeros
then it's not feasible.

Can you help..This is the last request..

We ( My Son and I ) greatly appreciate the support.

 

[Frank Kabel]

Hi
you may explain what you're trying to achieve with this new formula:
- check if the cell is empty
- check if the cell conatins an error
- check if the cell is zero