Forum Discussion

Kira_090120's avatar
Kira_090120
Copper Contributor
Nov 06, 2020

How to show min value

Ok some context first, 1.) I’m a newbie so I’m sorry if this post is stupid 2.) I have a general ledger that I can input my daily expense or income in. I’m trying to create a dynamic text that allows me to see what my highest expense is for each month. I want to be able to filter it by different months and by years. Is this possible and how do I create that?
Charting
excel
Formulas and Functions
Macros and VBA

1 Reply

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    Nov 06, 2020

    Kira_090120 

     

    :Caveat:

    Since you did not share data with us therefore I've used a sample data, will help you to find month & year wise lowest (minimum) value. 

     

    How it works:

    • Enter 01/01/2020 in cell N16 & 02/01/2020 in cell O16 and 01/01/2020 in cell R16.
    • Apply mmm, yy cell format on N16:O16 and yyyy on cell R16.
    • To get unique item an array (CSE) Formula in cell M17: 

     

    {=IFERROR(INDEX($O$2:$O$13,MATCH(0,COUNTIF($M$16:M16,$O$2:$O$13),0)),"")}

     

     

    Finish the formula with Ctrl+Shift+Enter & fill it down.

     

    • An array (CSE) Formula in cell N17 to get minimum values:

     

    {=IF(MIN(IF(MONTH($N$2:$N$13)=MONTH(N$16),IF($O$2:$O$13=$M17,$P$2:$P$13)))=0,"",MIN(IF(MONTH($N$2:$N$13)=MONTH(N$16),IF($O$2:$O$13=$M17,$P$2:$P$13))))}

     

     

    Finish the formula with Ctrl+Shift+Enter & fill it across.

    • Get Unique list in cell Q17:Q19 ( use formula as shown in cell M17 ). 
    • CSE Formula in cell R17:

     

    {=IF(MIN(IF(YEAR($N$2:$N$13)=YEAR(R$16),IF($O$2:$O$13=$Q17,$P$2:$P$13)))=0,"",MIN(IF(YEAR($N$2:$N$13)=YEAR(R$16),IF($O$2:$O$13=$Q17,$P$2:$P$13))))}

     

     

     Finish the formula with Ctrl+Shift+Enter & fill it down.

     

    • You may use other aggregate functions also like MAX/AVERAGE.
    • Adjust cell references in the formula as needed.

Resources