Array formula with minimum value greater than 0 and an additional condition

[deedledoo]

Hi All,

I've been trying for a little while now through research and I'm finally stumped. My excel needs have usually always been solved through searching, learning and then modifying what I've found, not this time.

Let's say that I have 3 columns. Column A is called range1 and column B is called range2. Column C is my formula.

In range1 I have groups of numbers (1,1,1,2,2,2,2,2,2,3,3etc), and in range2 I have prices. Sometimes, range1 will have 3 entries in the group, sometimes 7. The price entries might be a combination of actual prices mixed with entries such as $0, 'N/A' (as text) or even just empty.

What I need to do is for each row, show what the difference is between that row's price, and the lowest price in that group, not including $0. I've tried
{=$b2-min(if(range1=$a2,range2))} but that includes $0 and doesn't give me an accurate report

I know it's possible because it's just logic but I don't seem to be able to capture it in the formula.

I appreciate your time in reading this.

 

[Scott Huish]

=IF(ISNUMBER(B1),B1-MIN(IF($A$1:$A$9=A1,IF(ISNUMBER($B$1:$B$9),IF($B$1:$B$9>0,$B$1:$B$9)))),0)

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[deedledoo]

Thank you very much. I ended up going with this
=IF(K2="","",IF(AND(K2<>0,ISNUMBER(K2)),K2-IF(SUMPRODUCT(--(range1=A2),--(range3="Yes"),range2)=0,MIN(IF(range1=A2,IF(range2>0,range2))),SUMPRODUCT(--(range1=A2),--(range3="Yes"),range2)),"N/A"))
as I needed to add another condition. The extra condition is that by selecting "Yes" in my extra column, I am selecting one entry out of the group as the winner and this entry may not be the lowest price. So the SUMPRODUCT just checks to see if there is a "Winner", if there isn't (it returns TRUE), and it uses your MIN(IF(range1=A2,IF(range2>0,range2))) to use as the "difference" for the group >0, but if there is a "winner", then it will use that winning entry from that group.

For anyone like me trying to google how to do multiple conditions on an array formula, it's each condition as an IF statement. Please correct me if I'm wrong.