HELP IF a cell is between certain numbers multiply by a certain figure...

[tandersondixon]

Hi everyone,

Apologies if this has been answered before.

I am creating a rebate calculator for my business. Some people get rebates based on every order (TYPE1) (i.e. £0.1), others get volume based rebates (TYPE@) across the year, i.e. if they order 1000 items they get £0.1, 2000 items £0.02, 5,000 items £0.3 and so on.

I have created a table for every order and for the TYPE1 it simply calculates order * rebate = total rebate and then sums this at the bottom to give a total.

For TYPE 2 I do not know how to make a formula. I was going to black out the rebate column for every order and just have a formula in the total column that calculates the rebate based on the order. However I do not know how to do this.

How can i make it so if the total lands between different numbers the rebate total multiplies by a different figure.

Thanks for your help in advance.

Tom

 

[VoG]

This may help you McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates