Hello everyone,
I would like to make a simple sheet that calculates the value of commission in few cases.
There are 5 people with different amounts of money and they want to buy some products, i would like to calculate the commission in the following cases:
(I give to "Quantity" field the abbreviation "QT" for an easier explanation.)
1.If QT <5 then commission is 0% of "Money to spend"
2.If 5<= QT <10 then commission is 5% of "Money to spend"
3.If 10<= QT <12 then commission is 6% of "Money to spend"
4.If 12<= QT <15 then commission is 7% of "Money to spend"
5.If QT >=15 then commission is 10% of "Money to spend"
There is an example of what i want to do :
Name |
Quantity |
Money to spend |
Commission |
X1 Y1 |
2 pcs. |
$ 1750 |
? |
X2 Y2 |
7 pcs. |
$ 1985 |
? |
X3 Y3 |
11 pcs. |
$ 1568 |
? |
X4 Y4 |
14 pcs. |
$ 1765 |
? |
X5 Y5 |
25 pcs. |
$ 1876 |
? |
I have tried to solve it with IF, but i didn't succeed.
What i did was something like :
=IF(B2<5,0,IF(5<=B2<10,5/100*C2,IF(10<=B2<12,6/100*C2,IF(12<=B2<15,7/100*C2,IF(B2>=15,10/100*C2)))))
Any help would be great.
Bookmarks