Lookup Formula: Commission Calculation To Be Done Automatically Once Data Is Inputted In Cell

Mar 25, 2009

I am trying to come up with a formula that will allow the commission calculation to be done automatically once data is inputted in cell A2 and E2. I have tried IF statements, but can not figure out how to make it work. I am not able to figure out how to get cells F9 and F19 to work with the proper formula.

View 4 Replies


ADVERTISEMENT

Formula / Function For Commission Calculation?

Jul 26, 2006

if i could get a hand creating a commission calculation.. here is what i'm looking for and my brain hurts trying to make it... I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for..

$0 - $10,000 - 60% commission
$10,001 - $15,000 - 65% commission
$15,001+ - 70% commission..

i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%.

ps.. my excel sheet is set up as follows:
Rows a-g (stuff that is irrelivant)
row h, gross fees
row i, commission (in dollars)

View 9 Replies View Related

Formula/Function For Commission Calculation

Jul 26, 2006

I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for..

$0 - $10,000 - 60% commission
$10,001 - $15,000 - 65% commission
$15,001+ - 70% commission..

i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%. any help is greatly appreciated..

ps.. my excel sheet is set up as follows:
Rows a-g (stuff that is irrelivant)
row h, gross fees
row i, commission (in dollars)

View 9 Replies View Related

Creating Commission Calculation?

May 15, 2014

I'm having trouble creating a commission calculation.

The commission schedule is paid on a tiered rate (below), and occasionally there will be recoveries due to client non-payment, which comes out of commission. There is also a budget variance every month. I'd like to have the Monthly Budget in cell A1, the Revenue in cell A2, Recoveries in cell A3 and the Commission (formula) in cell A4. I only need the formula for cell A4, everything else will be entered manually.

Revenue Schedule
$0-$14,000 = 0%
$14,001 - $18,000 = 7.5%
$18,001 - $BUDGET = 10%
$102% BUDGET - Forever = 15%

Note that once you hit 18K, the 14-18K does not jump up to 10%, it stays at the 7.5%.

View 7 Replies View Related

Sales Commission Calculation

Jan 4, 2010

I need to know what formulas to put into the cells in excel to make the following sales compensation example compute properly:

Time
Period Draw
Paid Actual
Commissions Owed to
Company Commission
Paid Total
Earnings Month 1$3,000$4,000-$1,000$4,000Month 2 $3,000$2,000$1,000-$3,000Month 3 $3,000$5,000($1,000)$1,000$4,000TOTALS$9,000$11,000-$2,000$11,000

View 9 Replies View Related

Weekly Average Commission Calculation

May 5, 2009

I hope this question has not been addressed but the closest I can find is in this link:
[url]

I am now a commission based contractor who started earlier this year & I want to be able to calculate my current average weekly income which should fluctuate greatly. I have a spreadsheet that works out what my current to date net income is but can't figure out how to break this down to a weekly avarage. My basic guess is that I want to take the figure provided and divide it by the number of weeks from "stated start date" to the current date (but on a divided by 7 basis?) to get my average weekly net income.

I am sure this is pretty simple for you all so I hope I am not wasting time it's just that if I try figure it out I am using something like WEEKNUM & that will fail after the next new year. -Although it would be better to base it all around the financial year if that can be done? (March 1st - April 31st in NZ)

View 10 Replies View Related

How To Lock Cell Once Data Has Been Inputted

Sep 16, 2011

Is it possible to lock a cell once data has been inputted into it??

I want to make a spreadsheet which has empty cells for reference numbers that once entered cannot be changed unless it is by the administrator.

i also want the spreadsheet to be accessed on multiple pc's by multiple users.

View 9 Replies View Related

Find The Commission Rate Per Worker Using Lookup Functions

Dec 24, 2005

I have a sheet listing comission rate eg. sales less than $200, the rate is
..5%, less than 300, the rate is 1%..etc.

Then i have another table showing different sales value of different workers. How do i find the commission rate per worker using lookup functions??

View 12 Replies View Related

Cell Lookup - Calculation Depending On Criteria Column?

Jun 29, 2013

Criteria
Column B

Constant
Variable0
Variable1
Variable2
Variable3

2
[Variable2/Constant]

5
123
43
45
76

[Code] ..

This is my table. What formula should I use so that Column B shows the calculation depending on the Criteria column?

View 9 Replies View Related

Commission Formula

Sep 15, 2009

I am trying to write a formula for my commissions spreadsheet, which calculates commission clawbacks based on a sliding scale. From my understanding I need a code that will calculate additions or deductions based on a range of probabilities.

For example, if I have a percentage figure that is below 8%, I would like to add 15% to the total commission earned.

Here are the ranges below:

8% or under+15%
8-13%+ 10%
13-17%0
17-22%-10%
22% or more-15%

If I say my % is in K5 and the monetary value is in I5, what formula would I type into L5 to calculate the amendment?

View 14 Replies View Related

Vlookup Formula Changing Lookup Range Automatically

Apr 9, 2009

As I copy and past my lookup formula down the page it is changing the lookup range which I think is what is giving me so many #N/A results. My first Formula is
=IFERROR(VLOOKUP(A2,Coors2!A2:D3765,3,FALSE),IFERROR(VLOOKUP(A2,'AB2'!A2:C13944,3,FALSE),(VLOOKUP(A2 ,'WM2'!A2:C4843,3,FALSE))))

Then for instance at line 59 the formula is
=IFERROR(VLOOKUP(A59,Coors2!A59:D3822,3,FALSE),IFERROR(VLOOKUP(A59,'AB2'!A59:C14001,3,FALSE),(VLOOKU P(A59,'WM2'!A59:C4900,3,FALSE))))

View 2 Replies View Related

IF / OR Formula For Calculating Commission

May 6, 2014

I am trying to create a formula that calculates multiple commissions based on profit margin. So here is what I'm looking to. If the profit margin is between 50 and 70% than there is an additional 2% commission, if it's between 70.01-100% profit margin, than it's an additional 5% here is the equation I have=IF(OR(E2>50,E2<70),D2*2%,(IF(OR(E2>70.01,E2<100),D2*5%)))but it's still calculating at the 2% even thought it's an 86% margin.

View 4 Replies View Related

Commission Formula Not Complete

Sep 21, 2006

Looking for a formula for a zero based commission structure. I am having trouble with the formula. I have attached a breakout of what I need and an explanation of the end goal.

View 2 Replies View Related

Computing Commission Formula

May 11, 2007

determining a formula to compute a sales commission.

Here is a sales scenario.

A $25 commission will be paid on sales between $50 to $150.
A $50 commission will be paid on sales between $151 to $300
A $75 commission will be paid on sales between $301 to $600

The sales person will enter the sale amount into column B. Column C should compute the total commission for multilple sales.

Example:

Column A Column B
Sales Commission
$50 $150 (which is the comm. for the combined sales)
$175
$360

View 6 Replies View Related

How To Add New Row With Inputted Data

Dec 26, 2011

On my soccer spreadsheet, I currently have a sheet for entering results, and I have a separate sheet where all the match data is housed. Below shows screenshots of this:

This is the sheet where the user can choose the teams, the season and enter the result of the match. In this example, Arsenal v Aston Villa has been selected from the drop down menus, and on the far right shows the result of the match (ignore the prediction result, not relevant). The user is able to change the result of this fixture using the 'Enter/Change Result' macro button.

The userform used to change a result:

The sheet where all the match data is housed:

So when the user changes a result, the data gets transferred to the results sheet above.

The problem is, as things stand, the user CANNOT enter new results. They can edit current results between two teams for any season in which a game has been played.

This is why I would like to know how to be able to do this. What this would involve is the user inputting a new result between any teams, and the data they enter within the userform getting transferred to the results data worksheet. Basically, everything revolves around the concatenated references which contain the team reference numbers and the season. A new row would have to be created for a new result.

For example, if I wanted to enter a result for Arsenal v Aston Villa for season 2011/2012 (not yet played), the user would select the relevant options in the drop down menus, enter the result, season and date - then that data would be dumped in the results worksheet within a new row in the right place. The data in the 'Lookup' column of the results sheet would need to update accordingly, so that the most recent to the oldest fixture is in chronological order (1v2-1: the '1' means the most recent fixture, currently that is present for the 2010/2011 fixture but would need to be updated to '1v2-2' if the new result is inputted.)

View 1 Replies View Related

Sales Commission Formula Required

Jan 14, 2009

I have a new sale structure to put in place the commission is paid in the following way:

below 1500 zero commission
between 1501 and 3000, commission at 16%
between 3001 and 8000, commission at 23%
above 8001, commission paid at 30%
Ergo if you generate 5000 you would be paid 700 ie nothing for the first 1500, 16% of the second 1500 and 23% of the remaining 2000. ( I hope my maths is correct! )


I have tried to manipulate other solutions using sumproduct but my knowledge is poor, the formula I have tried manipulating is =SUMPRODUCT( (A2 > {0,1500,3000,8000}) * (A2 - {0,1500,3000,8000}) * {0,0.16,0.23,0.3}). I prefer single line formula rather than lookups as staff will not be able to see commission rates easily.

View 4 Replies View Related

Step Based Commission Formula

Sep 7, 2006

I am having trouble with the code for this stepped scale commission structure.

Net Service
$1,1400

Step Scale Comm. %
$0-500 40%
$500-750 45%
$750-1000 47%
$1000-9999.99 50%

Final Commission Paid $ ?

View 7 Replies View Related

Nested IF Formula: Calculate The Commission For My Employees

Feb 22, 2007

I am trying to write a command to calculate the commission for my employees. There commission is based on the spread between sale price and cost. For example:

If Profit is between $1.00 and $2.00 - commission = 15%
If Profit is between $2.01 and $4.00 - commission = 20%
If Profit is between $4.01 and $6.00 - commission = 25%
If Profit is > than $6.00 then - commission = 30%

I am able to calculate the first level ex: =IF((C3-B3)<=2,"15%") It Displays the 15% in the formatted cell. (C3-B3 is the profit spread). How can I include the other 3 commission levels in the formula to display the correct commission % based on profit spread?

View 5 Replies View Related

Userform To Post Inputted Data Into New Row

Oct 24, 2013

I am creating a 'add supplier' userform, in which i open the userform, type in the new suppliers information, then press submit.

The problem is that the data i enter deletes the data that i previously entered into row 26 instead of posting the data into a new row (I wish it to be 27, then 28, etc)

Here is my current code for the userform

VB:

Private Sub CommandButton1_Click()
nextrow = 26
Sheets("Suppliers").Cells(nextrow, 3) = UserForm5.TextBox1.Value
Sheets("Suppliers").Cells(nextrow, 5) = UserForm5.TextBox2.Value
Sheets("Suppliers").Cells(nextrow, 6) = UserForm5.TextBox3.Value
Sheets("Suppliers").Cells(nextrow, 7) = UserForm5.TextBox4.Value

[Code]....

View 2 Replies View Related

Formula For Range To Calculate Commission Paid In Month

Feb 26, 2014

I have attached a spreadsheet which shows my desired result which often works.

Range C2:C13 is my desired result...

The problem /criteria is...

For every 140 meters sold the sales person receives £140 commission.

Meters (range B2:B13) can be carried to future months.

I am looking for a formula for range C2:C13 which calulates if commission should be paid in this month or not and if so, how much.

View 5 Replies View Related

Making A List Of Names From Inputted Data

Nov 10, 2008

1. Is it possible for this hyperlink box to only be displayed when ALL the questions have been answered? i.e. All drop down boxes have been used?

2. Is it possible for the result (along with their individual names and scores) to be saved on another sheet? This of course would be supplemental (i.e. When the next person finishes the test, their name would be next on the summary etc.)? I would then have a whole list of who had taken the test...when...and what their score was!

3. Finally, and I know I'm pushing my luck, but is it possible for this summary sheet of names and scores to be hidden?

View 9 Replies View Related

Adding Inputted Value To Another Cell

Mar 8, 2009

I had already asked a question like this on the forum and was unable to get a working answer. I'm still grateful at the time JBeaucaire spent trying to help me. The solution we had worked in Excel 2003 but when moved into 2007, an error came up. To see the solution and the error, the previous post is at

HTML http://www.excelforum.com/excel-2007-help/663593-adding-function.html

What I'm trying to accomplish is if a person inputs a number into a cell, A1, the value will be added to another cell, A2, then A1 will be cleared. If another value is inputted into A1, then it will be added to the value into A2 on top of all the other values inputted and clear A1 again.

View 7 Replies View Related

Convoluted Sum Formula (formulas To Generate Commission Reporting Information On The Summary Tab )

Feb 27, 2009

I need 2 different formulas to generate commission reporting information on the Summary tab of the attached sample Excel file. The first is highlighted in green. For these cells, I need a sum formula that reports the total commissions (column H of the "Data" worksheet) for items Ordered in the month listed in column B of the "Summary" worksheet, but not invoiced until the month listed in the column D, E & F headers of the same worksheet. Date of item order can be found in column A of the "Data" worksheet. Date of invoice can be found in column E of the "Data" worksheet.

Now, the problem that I think I am going into is the way Excel handles dates and times. All columns and data highlighted in orange on the data sheet need to be maintained without being changed, as eventually I am going to have a report setup by our operating program drop in there so that it automates the information without any additional labor by our employees who have varying levels of Excel proficiency. Unfortunately, the report from our operating program cannot simply list a date without a time. Feel free to create any column or field to the right of the orange columns in order to complete formulas based on those orange columns. I will just lock those cells when finished so that coworkers don't accidentally blow the shizel up.

The second sum formula that I need is highlighted in yellow on the "Summary" worksheet. Basically, I need a formula that sums all commissions in column H of the "Data" worksheet for those items that are cancelled AFTER invoicing. Column D of the "Data" worksheet lists the cancellation date. There are explanations for each of these on the worksheets for quick referral.

View 2 Replies View Related

Display Text In A Cell That Goes Away Once Something Inputted?

Jun 18, 2013

I am making a list in excel and there are multiple cells where I want the user or customer to fill in their information. For example, In C2, the customer is supposed to enter the name in. How I can put text in this cell that will automatically disappear once they start editing the cell?

i.e. [Manual Entry]

View 3 Replies View Related

How To Display Text In A Cell That Goes Away Once Something Inputted

Jun 18, 2013

I am making a list in excel and there are mutliple cells where I want the user or customer to fill in their inormation. For example, In C2, the customer is supposed to enter the name in. How I can put text in this cell that will automatically disappear once they start editing the cell?

i.e. [Manual Entry]

View 1 Replies View Related

Hide Rows Based On Cell Value Inputted From Another Tab

Dec 13, 2013

In this example on Sheet 2, I'm trying to hide rows 1-6 if 'E6' is 0, and hide rows 15-26 if 'E18' is 0. These cell values are equal to the same cell in Sheet 1.

I've tried different codes I've found online, but I'm not very experienced with VBA so I can't get it to work. I can get rows 1-6 to hide, but it doesn't do it automatically whenever a zero is inputed into Sheet 1. It only works if I enter something into another cell on Sheet 2.

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E6") = 0 Then
Rows("1:6").Select
Selection.EntireRow.Hidden = True

[Code]...

View 5 Replies View Related

Formula To Skip Over That Data Point In The LINEST Calculation

Sep 4, 2009

I have instances where my Y variables sometimes contain a zero in the data and i need a formula to skip over that data point in the LINEST calculation.

Y variables are in Row 1, Columns A:E
X variables are in Row 2, Columns A:E

The following formula is returning a #VALUE! error: .....

View 9 Replies View Related

Changing Auto Filter Based On User Inputted Cell

Apr 7, 2007

I have a Workbook that has 2 worksheets, one called CALL QUERY and one called CALL LOG. On the CALL QUERY sheet, cell D9 is a user inputted cell with the cell validation set to list. The user uses the dripdown list to pick an office identifier (3-Letter Code).

On the CALL LOG sheet, I have info about individual calls. Column E has the 3-letter office identifier for each call.

I am trying to find a way to automatically auto filter the list on the CALL LOG sheet with the user input in cell D9 on the CALL QUERY sheet.

I was given the following (N2 is a cell on the CALL LOG sheet that equals the user inputted cell D9 on the CALL QUERY sheet)

View 14 Replies View Related

Add Shape On Cell Formula Calculation Value Change

May 28, 2008

I am trying to get different shapes (previously created) to appear in a certain cell, dependant on the resultof a formula in an adjacent cell.

View 4 Replies View Related

Lookup Formula: Find The Longitude And Latitude Data From My "lookup" Sheet

Jan 28, 2009

In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved