formula works ok on my Excel 2003


On 2 Cze, 13:27, EricB wrote:
I tried both options:

=MAX(IF('Raw Data '!$A$2:$A$5000=A2,'Raw Data '!$J$2:$J$5000,"")) ...... Returns a #VALUE error
=MAX(IF(A$2:A$5000=A2,'Raw Data '!J$2:J$5000)) ..... Returns the maximum value of '20090530' for all the cells.

I substituted A2 with the numeric value but with no change.

Regards

EricB



"Jarek Kujawa" wrote:
with a list of unique company codes in col Z

=MAX(IF($A$1:$A$100=Z1,$J$1:$J$100,"")
arra-enter this formula i.e. use CTRL+SHIFT+ENTER to insert it instead
of using just enter

then copy/drag down

pls click YES if it helped

On 2 Cze, 11:32, EricB wrote:
Im trying to get MAX to return a latest date/highest value but the same date
is returned in each cell;

In column €˜A I have company codes, in column €˜J I have dates in which
business was done with the company specified in €˜A.

Â* Â* Â* Â* Â* Â* Â* Â* A Â* Â* Â* Â* €¦€¦ Â* Â* Â* Â*J Â* Â* Â* Formula result
1 Â* Â* Â* 8000 Â* Â* Â* Â* Â* Â*20070530
2 Â* Â* Â* 8000 Â* Â* Â* Â* Â* Â*20080530
3 Â* Â* Â* 8000 Â* Â* Â* Â* Â* Â*20090530 Â* Â* Â* Â*20090530
4 Â* Â* Â* 8050 Â* Â* Â* Â* Â* Â*20070530
5 Â* Â* Â* 8050 Â* Â* Â* Â* Â* Â*20080530 Â* Â* Â* Â*20080530
6 Â* Â* Â* 8100 Â* Â* Â* Â* Â* Â*20040530
7 Â* Â* Â* 8100 Â* Â* Â* Â* Â* Â*20050530
8 Â* Â* Â* 8100 Â* Â* Â* Â* Â* Â*20040530 Â* Â* Â* Â*
9 Â* Â* Â* 8100 Â* Â* Â* Â* Â* Â*20060530 Â* Â* Â* Â*20060530

Presently MAX & MAXA is returning 20090530 for all cells, how can I obtain
above results as indicated under €˜Formula result?

Regards

EricB- Ukryj cytowany tekst -

- Pokaż cytowany tekst -