lundi 5 avril 2021

Service charge based on amount purchase

I am trying to find a way to capture the amount I charge for a service based on the amount the client purchases. I charge $4 for every $1 to $50 and $7 for every $51 to $100. So if the client purchases a service worth $120 then i'll charge $for the first $100 and $4 for the $20. Another example, if the client purchases $950 i'll charge 97 + 14 = $67. However, i charge $50 for every $1000 of service, so if the client spends $1,150 then i'll charge $50 + $7 + $4.

I tried creating a formula in Excel but i am looking for a much efficient way of doing this. My formula is =IF(C1<= 50, 4, IF(C1<=100, 7, IF(C1<=150, 11, IF(C1<=200, 14) ETC...

There are times when the amount spent is over $5000, an IF function like the one above is not efficient to me. I wondered if there was another way of doing this. I thought about a Vlookup but even that would be time consuming.

Aucun commentaire:

Enregistrer un commentaire