• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Find MIN, MAX and AVERAGE values in an array based on criteria and excluding zeros

In the screeshot below, I want to find the minimum value for 'apples' but ignore zeros.  In this case, the result should be 4.

Also need the max and average values for the same criteria.

Attempted to use this array formula but it returns zero.

=MIN(IF(C1:C14<>0,A1:A14))*(A1:A14="apples")

Open in new window

screenshotBook1.xlsx
0
mcnuttlaw
Asked:
mcnuttlaw
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Try

=MIN(IF((C1:C14<>0)*(A1:A14="apples"),A1:A14))
0
 
mcnuttlawAuthor Commented:
Still returns a zero (using the attached sample worksheet).
0
 
Saqib Husain, SyedEngineerCommented:
Sorry change it to

=MIN(IF((C1:C14<>0)*(A1:A14="apples"),C1:C14))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now