We are building Sales App.
We have column that shows total amount of sales in typical month. Example oct 2020
We want to add new column with Commission Percentage which work on following logic
If total sales 0 to 100,000 then value is 10
If total sales lie between 100,001 to 200,000 then value is 12
If total sales lie between 200,001 to 300,000 then value is 13
and so on…
Kindly guide how to do that
Couldn’t you just create an IF/THEN column that checks those conditions and returns the percentage you need?
Yes I created the problem is as follows
When total sales is 55,000 and logic implemeted is IF total sales is less than or equal to 1 Lac then value is 10
but when total sales is 150,000 then what logic shall I put, if I put if total sales is equal or greater than 100,001 then value is 12 but what will happen if total sales is 250,000?
Start with your largest number. If it’s greater than that number, it will return a value. Else it will check your next largest number. If it fits in that condition, then return a value. It will just check from largest to smallest until it finds a range that it fits in.
okay let me try
You got some weird stuff going on there with intermixed ranges and intermixed greater and less then. It should all be largest to smallest numbers and all greater than or equal for the comparison. Something like this.
In your example every number fit into the <= 1000000 condition first, so that’s why they all returned 19.
Solved, Thanks
I have a sheet which have three major columns
I am looking to add a column for commission with logic as mentioned above.
I am looking to Create a chart which shows commission earned in typical month
Kindly advise the best way to do it.
Ey @Adviuz it is useful for the community, and also a kind way to say thanks, to tag as “Solution” the answer that helped you with your problem…
