• 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.

Use of Max,Min,Average formula

S

sachar

Member
Dear,

With reference to the attachment, I applied MAX; MIN; AVERAGE formula with selected range/ period manually according to the mentioned above (01-Mar-17 to 15-Mar-17).

The further request, I want to get the value automatically by the change of the period with the help of a formula.
 

Attachments

  • Sample file_period_max_min_average.xlsx
    9.8 KB · Views: 5
Top: =MAX(IF($A$2:$A$30>=$F$3,IF($A$2:$A$30<=$G$3,$B$2:$B$30,)))
Best: =MIN(IF($A$2:$A$30>=$F$3,IF($A$2:$A$30<=$G$3,IF($B$2:$B$30>0,B1:B29,))))
Average: =AVERAGE(IF($A$2:$A$30>=$F$3,IF($A$2:$A$30<=$G$3,IF($B$2:$B$30>0,B2:B30,))))
with all formulas above press Ctrl+Shift+Enter, not just Enter

If you have a newer version of Excel (Office 365 or Excel 2016+) you can use Averageifs(), Minifs() & Maxifs() functions

=Maxifs($B$2:$B$30, $A$2:$A$30, ">="&$F$3, $A$2:$A$30, "<=" & $G$3)
=Minifs($B$2:$B$30, $A$2:$A$30, ">="&$F$3, $A$2:$A$30, "<=" & $G$3, $B$2:$B$30, ">"&0)
=Averageifs($B$2:$B$30, $A$2:$A$30, ">="&$F$3, $A$2:$A$30, "<=" & $G$3, $B$2:$B$30, ">"&0)

These 3 functions are entered normally with Enter

Note that you have to remove the 0's from the Min and Average functions in both examples
 
Last edited:
You must log in or register to reply here.
Back
Top