Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel maxa | Excel Discussion (Misc queries) | |||
MAX, MAXA functions | Excel Discussion (Misc queries) | |||
MAX & MAXA returning 0 value | Excel Discussion (Misc queries) | |||
Code required for VLookup returning #NA | Excel Discussion (Misc queries) | |||
Countif, Maxa or other function? | Excel Worksheet Functions |