Forum Discussion
Help : Formula for different condition
See attachement.
In this file, you insert the ID and the spent dollars in column A and B.
In column C D and E it counts how many of the biggest rate is applyable to the points of the customer, then calculates fidelity points in column F.
It calculates the integer solution of total/3000, then subtract that 3000 points (for each) from the original total and calculates the integer newertotal/2000; than do the same with newesttotal/1000. So 4000 is 1 time 3000 (100) and 1 time 1000 (20) [that is obv the same of 2 times 200 (60+60)]; 6000 is 2 times 3000 (200) that is better than 3 times 2000 (180) or 6 times 1000 (120). Fair enough.
I added a G column in which it shows how many points are "left", for the next goal.
You should hide columns C D E (colored columns).