CODEWITHSUNDEEP
excelexcel-formulamin
nina12345
nina12345

Reputation: 11

how to find minimum value (excluding zeros) from multiple ranges of cells

Thank you for reading my question. I am trying to find the minimum value of a few ranges of cells excluding zero. I know if it is a contiguous range that you can do small(countif(,0)+1). However, I am getting a #value error when I try to have multiple ranges. For example =SMALL((K32:K42,W32:W42,AI32:AI42,AU32:AU42),COUNTIF((K32:K42,W32:W42,AI32:AI42,AU32:AU42),0)+1). I am trying to find the minimum value of K32:K42,W32:W42,AI32:AI42,AU32:AU42. I appreciate any help! Thanks!!

Upvotes: 1

Views: 1962

Answers (3)

Pradeep
Pradeep

Reputation: 1

=min(unique("your range"),2)

This gives minimum nonzero number from a range.

Upvotes: 0

bosco_yip
bosco_yip

Reputation: 3802

Or try this non-array formula proposal,

In I11, enter formula :

=1/AGGREGATE(14,6,1/CHOOSE({1,2,3,4},C2:C8,G2:G8,K2:K8,P2:P8),1)

enter image description here

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152495

Use CHOOSE in an IF to create an array and pass it to MIN:

=MIN(IF(CHOOSE({1,2,3,4},K32:K42,W32:W42,AI32:AI42,AU32:AU42)>0,CHOOSE({1,2,3,4},K32:K42,W32:W42,AI32:AI42,AU32:AU42)))

Depending on one's version this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

Upvotes: 2

Related Questions