Forum Discussion
Multiplication and cut-off value
There is no reason to limit the solution to the latest Office 365 features.
The following is a KISS implementation. See the attached file.
For customer D, the formula in D5 is:
=MIN(B5*C5, IFERROR(VLOOKUP(A5, $F$2:$G$6, 2, FALSE), 1E+300))
If the customer name in column A is found in column F, VLOOKUP returns the corresponding value in column G. For customer D, the formula is effectively MIN(B5*C5, G3). If B5*C5 exceeds G3, MIN returns G3; otherwise, MIN returns B5*C5.
If the customer name in column A is not in column F, the formula is effectively MIN(B2*C2, 1E+300) for customer A, for example. Since 1E+300 is larger than any reasonable calculated amount, MIN always returns the product B2*C2.
Note: 1E+300 can be any "very large" value that is easy to remember. For example, 1E+100 might be easier.
There is no reason to limit the solution to the latest Office 365 features.
The following is a KISS implementation. See the attached file.
If you are interested in backward compatibility or simply leveraging the tools you have available then by all means revert to traditional spreadsheet techniques, though I beg to differ in describing them as 'simple'; concepts like relative referencing are not 'simple', merely 'familiar'.
For me, the elimination of unnamed references and formulas that address single elements of an array (followed by massive replication) are themselves major objectives. Traditional techniques are known (see Panko, EuSpRIG) to give error rates in the region of 90% for spreadsheet solutions, and anything capable of reducing this error rate needs to be considered.
I introduced the idea of a table, which you adopted, as a device for getting the limits, and the customers to whom they apply, visible on the worksheet rather than hidden within a formula. The idea of unlisted companies being without limit is fine but, again, more simply implemented using MS365
= LET(
cost, quantity*rate,
cap, XLOOKUP(customer,customerList,limit,"∞"),
IF(cost<cap, cost, cap) )Note: The use of "∞" is mere affectation, any text or large number would work equally well.