Results 1 to 10 of 10

Looking for the INDEX of 1st and 2nd largest values of a give MONTH

Threaded View

  1. #1
    Registered User
    Join Date
    05-21-2014
    Location
    Saudi Arabia
    MS-Off Ver
    2021
    Posts
    38

    Question Looking for the INDEX of 1st and 2nd largest values of a give MONTH

    hi,

    I am struggling with formula to return the index of 1st largest and 2nd largest value of given month.

    I'm using this array formula to get 1st and 2nd largest values
    Formula: copy to clipboard
    ={LARGE(IF(B1:M1=O1,B2:M13),1)}


    and this formula to get the index of

    Formula: copy to clipboard
    =IF(Q2<>0,IF(LEN(Q2),INDEX($A$2:$A$13,SMALL(IF(B$2:M$13=Q2,ROW($1:$12)),COUNTIF(Q$2:Q2,Q2))),""),"")


    but this formula is not return correct index of 1st and 2nd largest values from given month.

    If you change month to
    JAN: its returning 1st largest value as 'product2' from column D , But it should return 'product8' which is 13 from column B i.e. JAN
    FEB: it is returning 1st largest value as 'product3 from column B i.e. JAN month, but it should return 'product5' which is 11 from column C i.e. FEB
    and so on...

    I'm attaching file for better understanding...

    Thanks!
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Add 1 month to a date to give the EOM of following month
    By STUARTXL in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-31-2016, 03:46 AM
  2. [SOLVED] sum product of 4 largest or else give a specific statement
    By DJ_Habs in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-26-2016, 01:56 AM
  3. Formula to give week number of a month according to date of the month
    By tukae in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-08-2016, 11:14 PM
  4. Replies: 4
    Last Post: 04-04-2015, 04:24 AM
  5. [SOLVED] formula to give either the first day or last day of the month
    By Panfergrrl18 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-11-2013, 07:21 AM
  6. Get largest value based on month selected
    By Aland2929 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-14-2013, 05:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1