Excel Formula - Using condition provided in cell itself

2
votes

I have some data in an Excel sheet for which I need to find the minimum among cell range A4:A10 provided that above output should follow a condition provided in cell B10.

Content of cell B10 is ">10"

The formula below works for me:

=MIN(IF(A4:A10>10,A4:E10))

But I need to write a formula that sets the condition by referring to the contents of B10 (not by directly writing >10).
Any help will be great. Thanks

2
excelexcel-formula
Will your criteria always be "> value of B10" ? - ApplePie
No ,also please note condition is also written as content - Pradeep
Then honestly I can't help you unless you allow for filtering. Using advanced filtering you can use such syntax and then you can probably figure something out from there but I'm not sure it's possible in a single function. - ApplePie

2 Answers

5
votes

If you could do some changes in sheet like adding criteria headers you could solve it as presented on the picture using the following formula:

=DMIN(A3:A10,1,B9:B10)

enter image description here

1
votes

The easiest way to do this without changing your constraints is to write a short VBA macro you can call after updating B10. The code below just builds the formula as a string and passes it to the .FormulaArray property of the cell.

Sub updateformula()
Dim s1 As Worksheet, condition As String, buildFormula As String
Set s1 = Sheets("Sheet1") 'update to match your workbook
condition = s1.Range("B10").Value
buildFormula = "=MIN(IF(A4:A10" & condition & ",A4:E10))"
s1.Range("A11").FormulaArray = buildFormula 'update formula address to match your workbook
End Sub

You can either assign a key combination to this macro or place a control button on the sheet to call it after you update B10.