Numbers formula assistance requested

On possible approach is to have a "tiered" calculation, where the first $100 is calculated at one rate, the next $900 at a lower rate, the next $2000 after that at a still lower rate, etc.

The formula in B2, filled down, is:

MAX(0,(MIN(100,A2)−10))×0.4
+MAX(0,(MIN(1000,A2)−100))×0.1
+MAX(0,(MIN(3000,A2)−1000))×0.04
+MAX(0,(MIN(10000,A2)−3000))×0.02

The formula is long, but actually quite simple. It helps to enter it with option-return line endings in the Formula Editor as shown. That way you can easily edit the upper and lower end of the tiers and the rates for each tier. To add a tier simply add a new "row" in the formula starting with the +. To remove a tier, just remove a "row" in the formula.

SG

Is this really what you want? The cost in the first tier gets kind of ridiculous and there are large jumps in the shipping cost at certain dollar amounts, kind of like when an offer is "free shipping for over $100" but you order only $99 worth of product and end up paying $20 additional for shipping. But the purpose of that kind of offer is to incentivize you to buy more stuff.

At $100, the shipping cost = 300% * $100 = $300!! If you meant 30% then it is only $3 shipping for a $10 order which you said was a problem for you. But 300% of $10 = $30 shipping which seems high. Neither seems right.

At $1,000, the shipping cost is $150 but at $1,001 it drops to $70.

There is no $0-$10 tier. No products at that price?

You might want to specify an exact amount for low-price orders, not a percentage.

If you have access to the database of past orders and the actual shipping costs for each, you might try making a chart of it and seeing if there is a pattern to it you can replicate with a formula. It will likely have several tiers where the formula will be different.