Help with IF formula to return values based on a value being within a range

[tomashley]

Hi

I need help with an IF formula

I need to calculate a charge based on the number of transactions for a customer. To explain, the customer gets charged £500 if they do 1000 orders. If they do 1001 they get charged £500 plus 1 times the second bracket- £0.40. Finally if they do 3001 orders they get charged £500 plus 1999 times £0.40 then 1 times £0.30. The volume of transactions varies each month so I need to be able to put the total orders in a field and have the rest calculate

Between 0-1000 orders per month £500
Between 1001-3000 orders per month £0.40 per order
3001 + Orders £0.30 per order

Hope someone can help, it's a little beyond me

 

[bschwartz]

Does this formula do what you need?

=IF(A2<=1000,500,IF(A2<=3000,500+(A2-1001)*0.4,1299.6+(A2-3001)*0.3))