Hi all,
I am trying to create a sales commission calculator for all the sales people in our office.
Just before I get into my exact problem, I will try and explain how the system works. The sales people get a certain base salary. They sell different products all of which have different profit margins. Every month, their commission does not kick in until they sell enough to
cover a certain "base amount" that is set for that month.
Here is where I am stuck. I can calculate the commissions on their sales without any issues. I have a cell (B50) that contains a default value of a negative number that equals the "base amount" for that month. This cell (B50) is a calculated field that is based on a percentage of their total sales for that month. Everytime, the sales person makes a sale and plugs it into the spreadsheet, the negative
number keeps getting reduced. The idea is as soon as the the cell hits "0", their commision kicks in as they covered their base amount for that month. I also have another cell (B27) that keeps a track of their total sales amount as they keep plugging their sales data into the spreadsheet.
What I need is an event that monitors B50. As soon as B50, becomes a positive number (>0), it should take the amount in cell B27 (the total sales at that point) and copy the value to a different cell (D27). What I need in D27 is the actual value of B27 and not the formula. This is required for me as I need the dollar amount at which the commission kicks in to calculate their total commission.
How can I do that? Is it even possible in excel.
Thanks for any help and please let me know if you need any further clarifications.
Thanks.
I am trying to create a sales commission calculator for all the sales people in our office.
Just before I get into my exact problem, I will try and explain how the system works. The sales people get a certain base salary. They sell different products all of which have different profit margins. Every month, their commission does not kick in until they sell enough to
cover a certain "base amount" that is set for that month.
Here is where I am stuck. I can calculate the commissions on their sales without any issues. I have a cell (B50) that contains a default value of a negative number that equals the "base amount" for that month. This cell (B50) is a calculated field that is based on a percentage of their total sales for that month. Everytime, the sales person makes a sale and plugs it into the spreadsheet, the negative
number keeps getting reduced. The idea is as soon as the the cell hits "0", their commision kicks in as they covered their base amount for that month. I also have another cell (B27) that keeps a track of their total sales amount as they keep plugging their sales data into the spreadsheet.
What I need is an event that monitors B50. As soon as B50, becomes a positive number (>0), it should take the amount in cell B27 (the total sales at that point) and copy the value to a different cell (D27). What I need in D27 is the actual value of B27 and not the formula. This is required for me as I need the dollar amount at which the commission kicks in to calculate their total commission.
How can I do that? Is it even possible in excel.
Thanks for any help and please let me know if you need any further clarifications.
Thanks.