Help me with a formula Please

Cydlamb 

To accommodate tiered pricing based on the quantity entered in column E, with variations depending on whether it's greater or less than certain thresholds, consider using an IF formula. Here's a formula that precisely addresses your needs:

=IF(E16>36000, E16*D16*I16, IF(E16>48000, E16*D16*J16, E16*D16*H16))

This formula is designed to respond dynamically to different quantity thresholds. If the quantity in cell E16 exceeds 36,000, it multiplies it by the unit price in cell D16 and the multiplier in cell I16. If the quantity surpasses 48,000, it calculates the sub-total using the price in D16 and the multiplier in J16. For quantities within the range of 36,000 and 48,000, it utilizes the multiplier in H16. For a practical example, let's say you have the following values: E16 = 25. D16 = 1,500. H16 = 60. I16 = 62.22. J16 = 64.44 By inputting these values into their respective cells, you will find that the formula in cell E17 returns a sub-total of 37,500. This calculation demonstrates how the formula adapts based on the quantity and the corresponding pricing structure.

Alternatively, if you have access to the LET function and XLOOKUP, you can simplify the formula:

=LET(
product, E16 * D16,
multiplier, XLOOKUP(product, $G$15:$I$15, G16:I16, 1, -1),
product * multiplier
)

In this version, the LET function helps organize the calculation into manageable steps, enhancing clarity and maintainability. The XLOOKUP function looks up the multiplier based on the product of E16 and D16, facilitating a more elegant and efficient solution. With these formulas at your disposal, you can efficiently compute sub-totals in your order form, tailored to the tiered pricing structure you require.