Forum Discussion

Eddie0101's avatar
Eddie0101
Copper Contributor
May 11, 2023

Excel - Count how many months a specific metrics may be highest

I am attempting to create a comment generator for a variety of KPI's and am looking for excel to use a month selected via dropdown and subsequently read off of a Pivot Table (can use standard table references if needed) to determine if x value exceeded x amount of prior months performance in order of prior months from selected month. 


Ex: Sales in Apr was 50,000

Supplemental: Mar 43,000, Feb 22,000, Jan 48,000, Dec 56,000, Nov 60,000, Oct 49,000

 

I would like Excel to count until the value is exceeded while using EOM formula for standardization on date formats: Using Conconate the formula would fill the X months identified from the data set:

Ex: Best Sales month in 4 months

Ex: =Conconate("Best Sales month in ",(formula)," months"

 

Thank you for any insight/assistance.

excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Silver Contributor
    May 12, 2023
    I think you can do this using XMATCH instead. So if you have a row of data and use a formula like:
    =XMATCH( [lookup Val], [row of Data], 1,1) -1
    that should tell you how many previous months are smaller than this value

Resources