Multiplication and cut-off value

[DJRM72]

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.

[#2]

With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.
* Knowing the Excel version and operating system would also be an advantage.


Thank you for your understanding and patience


Nikolino
I know I don't know anything (Socrates)

[#3]

@DJRM72 

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.

[#4]

@NikolinoDE Thanks for responding. I have attached an example. The operating system is Windows 10, I am unable to access the Excel version.

[#5]

@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?

[#6]

@DJRM72 

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)

[#7]

@DJRM72 

[#8]

Perfect! Thank you very much.

[#9]

Thank you!

[#10]

@DJRM72 

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.

[#11]

Is this "multiply a quantity of 50 by a rate of €10 but give a cut off value of €100?" a general rule or as you have written that, "Customer D has agreement that maximum charge is €100",,, so do you have any such list? Please be specific all about CUT OFF method/mechanism ,, also edit your post & add complete information !!

[#12]

@Joe User & @DJRM72 

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.

[#13]

@Rajesh_Sinha 

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.

[#14]

---

There is no reason to limit the solution to the latest Office 365 features.

The following is a KISS implementation.  See the attached file.

---

@Joe User 

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.

[#15]

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 !!