Tiered Point Structure Loyalty Program

[pineappleblonde]

I'm creating a loyalty program and in order for me to do this I need to forcast some numbers.

I need to create a formula that calculates the number of points a customer receives on spend.

Tier 1 - $1 to $149.99 (needs to round down, so for 149 they would only earn 149 points) multiple this by the tier muliplier of 1.
Tier 2 - $150 to $499.99 Tier multiplier is 2
Tier 3 - $500 and above. Tier multiplier is 2.5

I am stuck and cannot for the life of me figure this out.

If a customer spends 150 they would essentaily earn 149 points from tier 1, and 2 points for the 1 dollar spent that falls into tier 2. making thier total points 151

[Bob Phillips]

Simple IFs should do it

=IF(A2<150,INT(A2),IF(A2<500,149+(A2-150)*2,149+600+(A2-500)*2.5))

[Sam Capricci]

A different take on it...
putting 0 in G2, 150 in G3 and 500 in G4, 1 in H2, 1 in H3 and 0.5 in H4 (the differences between each tier points)
and your data beginning in A2...
=SUMPRODUCT((A2>($G$2:$G$4))*(A2-($G$2:$G$4))*($H$2:$H$4))
It gives slightly different results than Bob's but looks correct.

[pineappleblonde]

Thank you for your help!

[Sam Capricci]

so, out of curiosity, which formula did you use, because mine and Bob's do not give the same results?