MAX/MAXA €“ Formula not returning required value
 

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

MAX/MAXA €“ Formula not returning required value
 

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
---------------
Jacob Skaria


"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

MAX/MAXA – Formula not returning required value
 

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:
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

MAX/MAXA €“ Formula not returning required value
 

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))

--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"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

MAX/MAXA – Formula not returning required value
 

On Tue, 2 Jun 2009 02:32:01 -0700, EricB
wrote:

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

MAX/MAXA €“ Formula not returning required value
 

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

MAX/MAXA – Formula not returning required value
 

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 -