MAX/MAXA – Formula not returning required value

[EricB]

I’m 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

 

[Jacob Skaria]

Try

if company code is numeric
=MAX(IF(A2:A100=8000,B2:B100))

if company code is text
=MAX(IF(A2:A100="8000",B2:B100))


If this post helps click Yes

 

[Jarek Kujawa]

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

 

[Jacob Skaria]

It is col J..

if company code is numeric
=MAX(IF(A2:A100=8000,J2:J100))

if company code is text
=MAX(IF(A2:A100="8000",J2:J100))

 

[Ron Rosenfeld]

I’m 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

As another alternative to generate this report, you could use a Pivot Table.

Label your columns something like: "Company" "Date"

Then Insert/Pivot Table and drag Company to the Row area, and Date to the Value
(or Data) area. Then right click in the Data area and select to summarize data
by "Max".

Your data above would give a result like:

Company Codes Latest Date
8000 20090530
8050 20080530
8100 20060530

--ron

 

[EricB]

I tried both options:

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

Regards

EricB

 

[Jarek Kujawa]

formula works ok on my Excel 2003

I tried both options:


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

Regards

EricB









- Pokaż cytowany tekst -