CODEWITHSUNDEEP
excelexcel-formula
Pradeep
Pradeep

Reputation: 3468

Excel Formula - Using condition provided in cell itself

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

Upvotes: 2

Views: 1028

Answers (2)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

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

Upvotes: 5

Excellll
Excellll

Reputation: 5785

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.

Upvotes: 1

Related Questions