Apr 16 2021 02:10 AM
Hi all,
I am trying to complete a table of suppliers on Excel Windows 10.
Some suppliers have a cut-off cost of €100.
So is there a formula that would multiply a quantity of 50 by a rate of €10 but give a cut off value of €100?
Thank you.
Apr 16 2021 03:02 AM
Apr 16 2021 03:13 AM
Since there is nothing specific to work on, a general idea might be
= LET(
cost, quantity*rate,
if(cost<threshold,cost,threshold)
If you are not using Excel 365 just calculate the cost twice within the final formula. If the cost and quantity are scalars rather than arrays, you could use MAX/MIN depending on whether the cut-off is an upper or lower limit.
Apr 16 2021 03:25 AM
@NikolinoDE Thanks for responding. I have attached an example. The operating system is Windows 10, I am unable to access the Excel version.
Apr 16 2021 03:40 AM
@Peter Bartholomew Thank you, that seems to work. Is it possible to limit the cut-off to particular rows - Supplier D in the attached example?
Apr 16 2021 03:48 AM
I still haven't quite got it ... but who am I? Here is an attempt in the inserted file.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
Apr 16 2021 04:31 PM
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.
Apr 16 2021 10:12 PM
Apr 16 2021 10:41 PM - edited Apr 18 2021 01:06 AM
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))
Apr 16 2021 11:33 PM - edited Apr 17 2021 12:10 AM
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.
Apr 17 2021 03:29 AM
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.
Apr 18 2021 01:02 AM