CODEWITHSUNDEEP
arraysexcelif-statementexcel-formulaconditional-statements
Alexandra Stevens
Alexandra Stevens

Reputation: 11

Create a multiple criteria, multiple row 'IF and MIN' conditional formula

I have a spreadsheet with values from a drainage program (pipes and pits) and I want to create a formula that will find the Pit name and then find the minimum value in the downstream end of the pipe that connects to that pit.

That I can do with an array - {=MIN(IF('ArcGIS Pipes'!$C$2:$C$100=A3,'ArcGIS Pipes'!$F$2:$F$100))}. However, sometimes the pit only connects to the upstream end which means it's in a different column under U/S IL (m). If that's the case then I want that value, but at the moment I'm having to first run the D/S IL (m) column formula, if that returns nothing then I have to manually change the null cells to {=MIN(IF('ArcGIS Pipes'!$B$2:$B$100=A2,'ArcGIS Pipes'!$E$2:$E$100))} which checks the U/S IL (m) column. Once I've done that any leftover null values are pits which don't connect to any pipes and they're manually given a null value.

So, what I want to do is have the one formula that does all of the above for me without me having to go through and fiddle things. In short I want the formula to first check for a minumum downstream value in one column, if that returns nothing then I want it to check for the minimum upstream value in another column, finally if that returns nothing then I want a null value returned.

I've fiddled around with nested IFs and sub arrays but just get an error or a FALSE returned. The formula I've tried that gives me FALSE is =IF((MIN(IF('ArcGIS Pipes'!$C$2:$C$100=A2,'ArcGIS Pipes'!$F$2:$F$100))),IF(MIN(IF('ArcGIS Pipes'!$B$2:$B$100=A2,'ArcGIS Pipes'!$E$2:$E$100)),null)). I know I'm missing something but I'm starting to go around in circles!

Screengrab - left table is the source table, right is the results table

enter image description here

Upvotes: 1

Views: 171

Answers (1)

user4039065
user4039065

Reputation:

This may be the array formula¹ that you are looking for. In K2 as,

=IF(ISNUMBER(MATCH(H2,C:C, 0)),
      AGGREGATE(15, 6, F$2:INDEX(F:F, MATCH("zzz",C:C ))/(C$2:INDEX(C:C, MATCH("zzz",C:C ))=H2), 1),
   IF(ISNUMBER(MATCH(H2,B:B, 0)),
      AGGREGATE(15, 6, E$2:INDEX(E:E, MATCH("zzz",B:B ))/(B$2:INDEX(B:B, MATCH("zzz",B:B ))=H2), 1),
      "no match"))

Fill down as necessary.

enter image description here

¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.

Upvotes: 1

Related Questions