In Excel is there a way to “turn on and off” the =min in a formula?

Question

I am creating a budget spreadsheet in Excel and in some instances I will need the formulas in the spreadsheet to include the =MIN and in some instances I won’t. For example, the formula is:

=MIN(221900, (H15+(H15*'Budget Details'!T18)))

I’d like to have a yes/no dropdown option, which will determine if this minimum is used, or if it isn’t. Is that even possible?

Without the =MIN, the formula is:

=(H15+(H15*'Budget Details'!T18))

Thank you for any guidance.

Answer 1

According to the information you provided, this should be easily achieved in combination with an IF() statement. Let's assume your data is stored in the range A1:A6 and you have the validation list in B1:B6.

In C2 you can put the following formula:

=IF(B2 = "Yes", MIN(500, A2), A2)

The formula checks if you have activated the Min. option in cell B2. If this is the the case, i.e., TRUE, it takes the MIN() of 500 and the value in A2. If the the Min. option is not acitvated in cell B2, i.e., FALSE, then it simply takes the value from cell A2. In your case, simply replace the A2 and minimum with your corresponding values.

The output looks as follows:

The data used is as follows; with the Min Option determined via a validation list Yes or No, and the values in column C determined via the formula specified above:

Values	Min Option	Formula
100	No	100
500	Yes	500
1000	No	1000
200	Yes	200
600	Yes	500