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.
In fact your formula is finding MINIMUM between multiple of B2 & C2 and the corresponding lookup value is Column G,, and in case of B returns 120,,, but since you have a table matching customers in column A then the used formula is irrelevant !!
This produces the same
=IFERROR(VLOOKUP(A2, $F$2:$G$6, 2, FALSE),B2*C2)Or even you may use,,
=IFERROR(VLOOKUP(A2, $F$2:$G$6, 2, FALSE),"")Or you may use this is the best one:
=IFERROR(VLOOKUP($A2,$F2:$F6,1,FALSE),IFERROR(VLOOKUP($A2, $F$2:$G$6, 2, FALSE),$B2*$C2))- Need to adjust cell references in the formula as needed.
- Check the attached WB.
- The method shown in the attached WB, works with all versions of Excel.
Re: ``your formula is finding MINIMUM between multiple of B2 & C2 and the corresponding lookup value is Column G``
Correct. And that is my interpretation of what DJRM72 wants. He wrote: ``is there a formula that would multiply a quantity of 50 by a rate of €10 but give a cut off value of €100?``
Of course, 50*10 is 500, not 100.
I might also note that DJRM72 did not push back against Peter's similar interpretation. On the contrary, DJRM72 said Peter's interpretation is "perfect".
DJRM72 also seemed to like Peter's idea of putting the customer limits into a table, instead of explicitly in each formula.
I only sought to offer an alternative that, at the very least, is compatible with all versions of Excel. I also believe it is the simpler implementation for all versions of Excel.
-----
Re: ``but since you have a table matching customers in column A then the used formula is irrelevant !! This produces the same =IFERROR(VLOOKUP(A2, $F$2:$G$6, 2, FALSE),B2*C2)``
No, it does not. See my example with customer F. B7*C7 is 200. Since that is less than the (hypothetical) limit of 220 for customer F (G4), my formula returns 200.
- Since you are referring data set for specific customer's cut off then in that case other doesn't matters,, please check the attached sheet with my reply !!
