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

Min / Max formulaes return 12:00 AM for non available parameter

Hi Champs,
Please help me as Min / Max return 12:00 AM for non available parameter......
kindly check summary tab ... row 7 ....
file attached
 

Attachments

Peter Bartholomew

Well-Known Member
As @vletm suggests, MIN or the more modern function MINIFS will return zero rather than an error if there are no matches.
Rather than trying to modify the formula, you might like to use a number format
[$-en-US]h:mm AM/PM;;"-";@
to conceal the zeros.

Something that might be of interest for the future (Office 365 in particular) is that the entire array of values can be created by one formula
= IF( alternate,
MINIFS( actualLogin, Code, summaryCode, Date, summaryDate ),
MAXIFS( actualLogin, Code, summaryCode, Date, summaryDate ) )


The main challenge is creating the alternating pattern of MAX and MIN.
 
Top