Subtotal based discounts

[ianonline]

Hello!

Can anyone help with these two?! I've searched the forum but not yet
found anything applicable or that works. I'm needing a formula to
calculate a discount. The amount discounted is a percentage based on
the amount in a "subtotal" cell.

Subtotal = J46
Discount = J47
Shipping = J48
Total = J49

It should go something like this, but I've had no luck so far, J46 is
the Subtotal cell.

=IF(J46>=40,20%*100,IF(J46>=30,15%,IF(J46>=20,10%,IF(J46>=10,5%,0))))

In other words, orders subtotalling over...

£40 get 20% discount
£30 get 15% discount
£20 get 10% discount
£10 get 5% discount

The formula above seems to ignore the percentage sign.

---------------------------------------------------------------

Ok, so that's one problem! Then there is this formula in J49:
=J46-J47+J48

However when the total value in J46-J47 totals over 50, then J48
(shipping) displays "FREE" in which case J48 should equate to zero, or
simply not be added to J49. Does that make sense?!! Any help is very
much appreciated, thank you.

Ian

 

[C01d]

Formlula for discount:

=IF(J46>40,J46*0.2,IF(J46>30,J46*0.15,IF(J46>20,J46*0.1,IF(J46>10,J46*0.05,0))))

Formula for shipping:

=IF((J46-J47)>50,"FREE",10)

Formula for total price:

=SUM(J48,J46)-J47

 

[Ron Coderre]

ianonline:

For
J46 containing the subtotal
J47 containing the discount amount
J48 containing the shipping amount
J49 containing the total

Here are some options

The discount:
J47: =CHOOSE(MIN(FLOOR(J46/10,1),4)+1,0%,5%,10%,15%,20%)*J46
or
J47: =(MIN(FLOOR(J46/10,1),4)*5%)*J46
or
J47: =(MIN(INT(J46/10),4)*5%)*J46

-----------
The shipping:
J48: =IF(J46-J47>50,"FREE",15)

-----------
The total:
J49: =J46-J47+N(J48)

Can you work with that?

Regards,
Ron