• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

excel max value found

U

ushaanu

Member
hi ,

i have some data in excel sheet . like in Column A i have end date against one product . now i need maximum majority End date month and year .

thanks
anu
 

Attachments

Hi Anu ,

I have not understood anything ; hopefully someone else will.

In the meantime , if you have time , can you explain how you got these results ?

Geeta .......... Jun-18
Neena .......... Jul-17
Teena .......... May-16

Narayan
 
Hi,

What I understood you want max repeating month and year for particular name. If so, see the attached file. Below formula is used.

1. Things to note (Use of Aggregate function so will be working in Excel 2010 & above).
2. Array function so must be enter be Ctrl+Shift+Enter.

Formula in I2. Copy till M4.

=IFERROR(INDEX($C$2:$C$38,AGGREGATE(15,6,IF(FREQUENCY(IF($G2=$B$2:$B$38,(MONTH($C$2:$C$38)&YEAR($C$2:$C$38))+0),IF($G2=$B$2:$B$38,(MONTH($C$2:$C$38)&YEAR($C$2:$C$38))+0))=MAX(FREQUENCY(IF($G2=$B$2:$B$38,(MONTH($C$2:$C$38)&YEAR($C$2:$C$38))+0),IF($G2=$B$2:$B$38,(MONTH($C$2:$C$38)&YEAR($C$2:$C$38))+0))),ROW(OFFSET($C$2,MATCH($G2,$B$2:$B$38,0)-2,0,COUNTIF($B$2:$B$38,$G2)))),COLUMNS($I2:I2))),"")

Just advise if you need any other thing.

Regards,
 

Attachments

@Anu

I am not sure what you are trying exact but as per my understand you want to max date of a name or item then try this Array formula (Ctrl+Shift+Enter) in H2=

=IFERROR(INDEX($C$2:$C$38,MATCH(MAX(($C$2:$C$38)*($B$2:$B$38=$G2)),$C$2:$C$38,0)),"")

hope it solve your problem other wise please inform

Thanks

SP

@Some

I Didn't use your formula due to i am using office 2007 and i don't know what type of result is came there
 
I think this version can be used in 2007.

In I2 copy across and down.

=IFERROR(INDEX($C$2:$C$38,SMALL(IF(FREQUENCY(IF($G2=$B$2:$B$38,(MONTH($C$2:$C$38)&YEAR($C$2:$C$38))+0),IF($G2=$B$2:$B$38,(MONTH($C$2:$C$38)&YEAR($C$2:$C$38))+0))=MAX(FREQUENCY(IF($G2=$B$2:$B$38,(MONTH($C$2:$C$38)&YEAR($C$2:$C$38))+0),IF($G2=$B$2:$B$38,(MONTH($C$2:$C$38)&YEAR($C$2:$C$38))+0))),ROW(OFFSET($C$2,MATCH($G2,$B$2:$B$38,0)-2,0,COUNTIF($B$2:$B$38,$G2)))),COLUMNS($I2:I2))),"")

Regards,
 
Hello Anu,

As every one said, I am not fully sure how did you get that out come. But, if your condition is as Misra said, try this Array formula with CTRL+SHIFT+ENTER in H2, then copy down.

=IFERROR(MODE(IFERROR(LARGE(IF(B$2:B$38=G2,C$2:C$38-DAY(C$2:C$38)+1),ROW(C$2:C$38)),"")),"")
 
You must log in or register to reply here.
Back
Top