Tracker
HOME    TRACKER    Excel

# Formula's For Calculate Sales Commissions

## I am trying to decipher how to calculate commissions for my sales reps. I have made just a simple spreadsheet to give you an idea of what I am doing. I have tried to us an IF formula but I think there are too many options( I have 9 reps). Basically I pay them either 10 or 15% so I need a formula to take the sales price - cost times their apporpriate %. AgentSales Price CostComm Pd AS150 75 JK255 185 JD325 250 JD125 50 AS50 10 AS50 10 AS335 250 JW75 25

Related Forum Messages:
Formula To Calculate Sales Tax From Total Sales
I have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and
filling in the chart on excel but I would like to create a formula that
automatically does it for me based on total sales.

Pivot Table: Calculate Percentage Of X Sales To To Total Sales
See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.

Formula- To Calculate The Amount Due Based On Cumulative Sales Once A Breakpoint Amount Is Reached
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.

Example:

Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%

month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00

and so on...until the end of year.

I tried using an if formula by could not get it to work.

Calculate Periodically Sales For New Products
I'm trying to calculate periodically sales for new products, which have been in the market for max 6 monts. After that 6 months the sales of the product is not to be calculated. I have a huge amount of products, where this information should be calculated, so manually calculating is not an option. The products are in rows, and periods are in columns. As the data concerns several years data there is a problem, that some products have in some months zero sales, and in the next month again some sales. This messes up always my calculations. How to truly take only the first 6 months, and leave all the rest uncalculated?

Calculate Weekly Sales With A Midweek Start
I'm trying to create a simple sales report. No VBA code, only excel formulas.
I'm stuck on trying to calculate the weekly sales. I want excel to be able to recognize the day of the week and know that the month started mid week.

Ex. If the 1st of the month started on a Wednesday, it adds all the sales from Wednesday to Saturday only and
if the month ends on a Tuesday, it will calculate the sales from Sunday to Tuesday only.
I want it done automatically.

I've included a zipped excel sheet example of the worksheet for a visual example.

How To Calculate Sales Based On Partial Cell Content (multiple Inputs)
I have made a dynamic calculator which will calculate the total sale based on 3 criteria in 3 different cells as follows:-

I would like to derive the sales qty in cell C5 if all the 3 conditions in the range B2:B4 is true.

In this case the sales qty for Style-Colour-MRP combination(9103-PINK-399) should be 6.

I am unable to figure out with the formula for the same....

Formula Won't Calculate When Only Some Of The Three Variables Are Input
In column H: =IF(ISERROR(L4-J4-K4),"",IF(OR(K4="0", K4=""), L4-J4, L4-J4-K4))
So, if formula is going to come up with an error, I don't need to know. If it's not an error: L - J - K. Except that sometimes there isn't information in J or K, and the formula won't calcluate L - 0 - 0, unless I physically enter in zeros.

=IF(ISERROR(L4-J4-K4),"",IF(K4=""), L4-J4, L4-J4-K4))
and
=IF(ISERROR(L4-J4-K4),"", L4-J4-K4))

I don't want the people using this to have to enter in a plethora of zeros in order to force the calculation. What am I doing wrong? I've attached workbook with the relevant information in it. You can see in the middle that if there is nothing entered, it won't work right.

Formula Won't Calculate On Specific Computer
We use a spreadsheet to log all transactions at our front desk. I've made a column that automatically calculates the time when a transaction is input into a row. The formula is as follows:

=IF(D5="","",IF(A5="",NOW(),A5))

This morning that formula stopped working on the computer at the front desk, returning a message about a circular reference. I can open that same spreadsheet on my computer as well as others in the office and it calculates fine, but of course on the one computer on which I need it to work, it won't ;P

All computers are using Excel 97. Presumably a setting has changed on the front desk computer that's affecting this calculation.

Determining Top Contributors To 50% Of Sales Based On Cumulative Percent Of Sales
I am trying to determine the top contributors to 50% of sales based on cumulative percent of sales (see attached file). I can determine if percent of sales is less than 50%, but I need to include the person that pushes the group of top performers over the 50% mark.

Sale Commissions Calculations
if sales target is say 1000
if they dont reach 60% no commission payable
if the reach 60% and over to sales target they get 3%
if they get over target they get 5% of everything over target

Summarize Monthly Sales From Daily Sales
I have daily basis monthly sales. Now I want to summarize into monthly gross. Pls look attached file. I am looking for a formula to summarize January daily sales from date 1st to 31 st as of just January and and sum of each day gross.

Sales Commission Formula Required
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.

'Calculate' Message In The Lower Bar Of The Window
Excel is very slow for me, and I have just noticed that there is a 'Calculate' message in the lower bar of the window.

Formula In A Cell That Configures Sales Tax
I have a formula in a cell that configures Sales Tax. How do I add to my existing formula so that if someone is tax EXEMPT to not display or calculate ANYTHING in the Sales Tax Cell? I want to add this to my existing formula in my sales tax cell: IF(Y33="YES") and then I want it to override any existing formula and display nothing in that cell at all.

Copy Week Total In Weekly Sales Worksheet To Appropriate Week In Monthly Sales
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).

Range I4:I28 to the monthly sales worksheet by date.

Conditional Formula- Worksheet With Monthly Sales Figures
I have a worksheet with monthly sales figures by associate and by store. The store has a monthly goal as do the associates. If the store hits it's goal then the overall sales total is multiplied by 1% and then divided by the percentage of each associates involvement to reach that goal. (ie...150,000*1%=1,500, John sold 35,000=23%, so John gets \$345 extra commission). If Johns goal was \$25,000 and sold \$35,000 he gets 1% or \$350 commission. In turn, if he meets 1 or both sets of criteria those will be added together. If he doesn't meet either one then the result is Zero.

I have the store goal and Johns goal in separate cells to reference against. The actual sales cell is formula based.

This is basically what i'm trying to do:
If criteria 1 is met then % of 1% of store goal, if criteria 2 is met then 1% of individual goal, if both are met result1+result2. if neither is met then zero. I think?

Run-time Error '1004' :: Method 'Range' Of Object'_Global' Failed
I am trying to create a macro in my personal macro book such that whenever any workbook is opened the calculation settings (tools, options, calculation tab) are set to semiautomatic and do not calculate before save. The macro works when I am opening Excel itself (Book1) but when I open an already saved file it gives me

Run-time error '1004'

Method 'Range' of object'_Global' failed

From there I choose Debug, the VBE window comes up, and I hit F5 to continue the code without doing any actual debugging. Here is the code that I am using. This is in the Personal Macro book on the "This Workbook" section....

Run-time Error '1004' Method 'Range' Of Object '_Worksheet' Failed
I have an interesting error that only happens when there is one row of data in the worksheet (sheet2 or "Half Payout"). Rows 1 & 2 are headers, row 3 is when the data starts - if any. With either no rows of data or more than one the coding works just fine. Here is the exact error message I'm getting: Run-time error '1004': Method 'Range' of object '_Worksheet' failed.

The following code is supposed to sort the rows of data when opened and then activate the first open cell below B2.

Run-time Error '1004': Methd 'Range' Of Object '_Global' Failed
I have constructed the following code to set the print area of worksheets that have been selected to print to the range referenced in a worksheet level named range "xPrintArea". This named range is set using the OFFSET function. The procedure also sets the left footer to be a copyright notice that is also contained in a cell referenced by a named range.

Set oPrintArea = Range(sPrintAreaName)

is generating the error "Methd 'Range' of object '_Global' failed".

Note that the line

does not generate this error. From what I've been able to determine from other research on this forum and others, I believe the problem is that I need to more fully qualify the object which Range(sPrintAreaName) is referencing. I've already tried to use

Set oPrintArea = wkSht.Range(sPrintAreaName).................

Run-time Error '1004': Method 'Worksheets' Of Object '_Global' Failed
I am not familiar with Excel/VBA and I tried a couple of suggestions with no luck. The excel file contains three forms and three modules and it is intended to calculate and build Electrical panels. I didn't write the code. I am just trying to fix the error. I was trying to attach the .xls file but it is 178 KB. How can I post the whole code.

Method 'Add' Of Object 'CommandBarControls' Failed (run-time Error '-2147467259')
i get this error on the line in bold Method Add' of object 'CommandBarControls' failed (run-time error '-2147467259'). im not sure why its giving that problem, but im not very fluent in custom toolbars.

'//The following two procedures add a custom menu to the workbook programmatically//'
'//and then delete it//'
Public currentMonth As String
Dim mybar As CommandBar
Dim myControl As CommandBarControl
Set mybar = Application.CommandBars.Add( Name:="CustomButtons", _
Position:=msoBarBottom, Temporary:=True)
mybar.Visible = True
Set myControl = mybar.Controls _
With myControl...........................

Using 'IF' With Conditional Format (change Cell Color Based On The 'P' Or 'F')
My cell has a 'P' or an 'F' plus some additional text. I'm trying to change cell color based on the 'P' or 'F' but I can't get it to recognize the letters.
Samples of what I've tried:
=IF(LEFT(L23,1)=P)
=IF(MID(L23,1,1)=P)
etc.

I can 'FIND' the 'P' or 'F' but there may be a P or F else where in the cell so the color may come up incorrect. I'm trying to get it to just look at the first character in the cell. Can the 'IF' be used with CFs?

How To Restrict The Cell Data To Letters 'a' Through 'e' AND 'A' Through 'E'
I am trying to come up with an excel shee which can track the progress of a student on practice exams. This exam only has multiple choice questions, with answer choices A,B,C,D or E.

What I am looking for

1) I want to restrict the cell data to A,B,C,D or E (both cases). Note that I am not looking for a drop down/combo box. It would take the user to use one or two mouse clicks to drop down the list, and I am not in favor of that.

2) I would like this validation/formula to be applied to all the cells in a particular column.

3) Nice-to-have.

If the user enters a lower case letter, then I would like to convert to upper case.

Can't Calculate Subtotals
have an excel spreadsheet linked to a network printer, it contains a list of what each user printed, how many pages, and the total cost. It is constantly updated. The total cost column contains the wrong price, so Im using this formula:

=If(C2=0,"",SUM(D2/C2)*(H2))

in EACH field on the total user cost column to extract the CORRECT price. So, I started my macro, highlighted the entire TOTAL COST column, then inserted my formula into each field in that column. The correct price displays for rows that contain data, and rows without data are blank. However, when I try to create a SUBTOTAL (Data --> Subtotals) for each user, I get the following error: "To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet."

This is because I applied the formula to the ENTIRE column - even blank cells still contain the formula. How do I get fields without data to be completely blank?

Using A 'what If' With A 'sum' Formula
I am trying to create a formula where it will add cells together from mulitple tabs within excel but it also needs to exclude any cell that currently has a "n/a" within that cell. I need to include these cells within the formula so if/when these cells change from "n/a" to a number, they will then be considered into the total.

Run-time Error '1004' Method 'Add' Of Object ' Sheets' Failed Adding Multiple Sheets
I have been running a simulation for about 18 hours now and just received:

Run-time error '1004':
Method 'Add' of object ' Sheets' failed

I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:

The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...

"Run-time Error '1004', Method ' Range' Of Object '_Global' Failed"
I keep getting this "Run-time error '1004', Method ' Range' of object '_Global' failed" Here is the code that has the problem:

Option Explicit
Dim i As Long
Dim j As Long
Dim lDup As Long
Dim lRow As Long
Dim NoDupes As Collection
Dim rRng As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Swap1 As Variant
Dim Swap2 As Variant
Dim wks As Worksheet

Private Sub UserForm_Initialize()
Call DefaultSet
For lDup = 1 To 3
Call NonDuplicatesList(lDup)
Next lDup
End Sub.....................

It does not even loop once though the original UserForm_Initialize For/Next loop.

Run Time Error 1004 - Method 'Range' Of 'Object'_Global' Failed
I am trying to build a macro which will format the columns of a spreadsheet - basically it inserts some columns, writes formulas and highlights them. Here is a code I have got so far...

When I try to run this I get a run time error 1004 - Method 'Range' of 'Object'_Global' failed. The part of the code
Range("N2:N").FormulaR1C1 = "=(RC[-7]/RC[-2])"

is highlighted in the debugger.

Can anyone tell me why this is happening, also it would be great if you could suggest better ways of writing this code - as I am new to vba programming and most of my macros are built using the recorder and then 'working' on them.

Formula- Working On A Spreadsheet To Control The 'booking Diary'
I've been working on a spreadsheet to control the 'booking diary' at work and linking it with the movements in and out of our 'chambers'

If you look at Column L & M, they are the volumes and column N is where the 'pallet' is coming / going to .

If you look at line 149 and below I've tried to translate these 'volumes' into movements by time but as you can see it is not to totalled up correctly during certain times.

For example line 158 should total 40 and line 187 should total 80.

Can anyone work it out for me as this happens on numerous lines

Formula- Making A 'sports Day' Spreadsheet For A Project
I am making a 'sports day' spreadsheet for a project and i have one problem, the spread sheet is meant to have the possiblitlity of mistakes reduced to a minimum.

I have 6 teams in the sports day and under each i put 1st, 2nd,3rd,4th,5th,6th.

[url]

basically i need the rows to only accept 1st 2nd 3rd..... only once. I need something like an error message to come up if a value is entered twice on a row.

Formula Not Understood: 'data Sheet' Means And Where To Find
I am very green with Excel. Can anybody tell me what 'data sheet' means and where to find it? =If('data Sheet'!\$G\$11=1,'data Sheet'!\$Bi\$197,'data Sheet'!\$Bj\$197)

List Of Sales By Salesperson From A List Of All Sales
I have one sheet that shows a list of all vehicle sales for a month: with a customer column and a salesperson column and a gross profit column. I would like to give a printout to each salesperson from a different sheet that only shows that salespersons transactions on it. Can excel parse that information out and list it in order row by row showing each sale for just one salesperson per sheet?

'INDIRECT' Formula Across Excel Versions
I have made a file that works perfectly in excel 2007, but when I send it to a client it doesn't work as they have 2003.

'greater Than >' Formula & Result
Column B is basically 550 points + column C = Base Roll.

We need to introduce a cap so no more than 1000 points can be displayed/earned. So column G adds all points & column F caps.

So far it's all ok.

Now when i add a value to column E (points spent) those points are deducted from column G instead of the capped 1000.

Entering a value of 500 displays 800 in column F instead of 500.

If Statement (put 'IF Formula' In Cells)
I am trying to put an "If statement formula" within cells using VBA but it errors at the word "error".

Basically what I am trying to do is put similar to the following example into a range of cells.. for example:

=IF(LEN(Verbatim_LU!B2)>100,"ERROR","OK")
...with B2 being worked out by the for..with and the value (in this instance 100) being put in by VerbLen.

NB: Column A contains a reference number hence not having this actioned on it and counts start from 2

Formula For A # In A Column Of #'s That's Closest To A Certain Val
I'm looking for a function or formula in Excel that will find a number in a
range of numbers that is closest to a specified number. Example: I have a
column of 7 cells that contain cummulative percentages from 0 - 100. It will
always be theses same cells, but will have varying percent values depending
on other calculations. I need to find the first number that's closest to and
below 10. I also need to find the first number that's closest to and above
10. I need this for 3 other values (40, 50, & 90)as well, but if I can get
an example of a formula, I could probably go from there.

If Formula: Identify New Products That Are Not In My 'stocklist'
I am trying to identify new products that are not in my 'stocklist'. In the 'new prices' sheet I have =IF(ISNUMBER(MATCH(C4,'Stock List'!C:C,0)),"","NEW") dragged down in column G. Though its throwing out 'New' on every item which is incorrect as there are loads of items already in both sheets. This sounds confusing but if you look at the attached im sure you will see what im trying to do.

One last problem is that some of the new codes have zeros in front of them so the formula will have to discount the 1st couple of digits IF they happen to be zero. example; C5480 in stock list is exactly the same as C398 in new prices. But because there is a zero in front of the C398 then the formula does not recognise that the two are the same.

Nested IF Command: Calculate Today's Date Minus
The range B7:K7 contains columns of "dates" and "characters" like "A", "B" and "C" . Range D2 = Today's date. If Range C7 is blank then it should calculate Today's date minus Column B7 (i.e D2-B7) so on:

B7 = "01-Apr-2009", C7 = "02-Apr-2009", D=7 = Status as "(A)"
B7 = Submitted Date, C7 = Received Date, D7 = Status
Difference Between Today's Date and "B7" date is required if "C7" is Blank date. Otherwise it should search next group (E, F)

=IF(AND(ISBLANK(C7),NOT(ISBLANK(B7))),D2-B7,IF(AND(ISBLANK(F7),NOT(ISBLANK(E7))),D2-E7,IF(AND(ISBLANK(I7),NOT(ISBLANK(H7))),D2-H7,IF(AND(ISBLANK(L7),NOT(ISBLANK(K7))),D2-K7,"--")))))

-- Excel File is attached.

Lookup With 'sheetname' In Formula
I am collecting a lot of prices from over 60 countries. Each country has a seperate sheet in a workbook, named "Country" Spec (eg Australia Spec, China Spec etc.).
These sheets are all setup exactly the same.

I have the following sheet wherin I want to show the data coming from all the different sheets:

| a | b
-----------------------------------
1 | Australia Spec | "Formula"
-----------------------------------
2 | Bangladesh Spec | "Formula"
-----------------------------------
3 | China Spec | "Formula"

In B1, 2 3 etc I want to do a lookup of a fixed cell on the correct country sheet. Therefore I want to include the sheetname in the formula. Thus I would like in B1 the content of cell q13 of sheet Australia spec. In B2 the content of cell q13 of sheet Bangladesh Spec.

My current formula is:
=INDIRECT(a1&"!\$Q\$13")

But it doesn't work

Multiple 'if' Scenarios In A Formula
What I am trying to do is a formula for two different 'if' scenarios that will return one value for one answer and another value for the other. They are not numbers, they are text.

For example:

if the cell contains 'pcs' value would = no
if the cell contains a dollar amt value would = yes

'Post Formula' Button
I may have missed it, but I can not seem to find where I can customize Excel to add a "paste formula" button. (I have a fair amount of copying to do where I do not want the formats being changed at the same time.)

How To Create A 'dynamic' Formula
I have a large spreadsheet file with numerous worksheets.
Each sheet represents one month of the year and is named Jan05, Feb05, Mar05
etc.

In the "Summary" sheet I want to automatically reference a range in the
other sheets.

For example:

In the Summary sheet column A, I have the sheet names:
Jan05
Feb05
Mar05
etc.

In column B, (let's use B5 as an example) I want a formula that reads
something like:
=B4 + Apr05!C7

This seems simple enough, but how can I write a formula that 'automatically'
enters the sheet name (i.e. "Apr05") in the formula?

The row containing the data for Apr05 is only created when that month comes
along, in order to keep the file size as small as possible at any time.

Run-Time Error '1004' Cannot Open PivotTable Source File 'Consolidated Report'
I have a macro that takes the date from different excel sheets, consolidates the data and renders the pivot table and chart accordingly. It was working fine when the date range defined for the pivot table was static. Now I have made it dynamic since the data range changes each month depending on the number of days it has got. When I run the macro, it runs succesfully, generates the report and save & close the report, but after that I am getting this error. Errorneous

Conditional Format The List So That It Shows The Lowest Percentage In 'green' And The Highest In 'red'
I have a list in column A of percentages (to 4 decimals 0.0013) i need to conditional format the list so that it shows the lowest percentage in 'green' and the highest in 'red'.

Error On Data Transfer Between Sheets (Method 'Range' Of Object '_Global' Failed)
I get an error message on the "Range(lstRow).Select line. (Method 'Range' of Object '_Global' failed). My goal is to transfer a specific range (C24:H24) to the first available row in Data Entry sheet.

Remove \$'s From Formula's
I have hundreds of formula which are linked to multiple spreadsheets. They all have \$ signs in which I would like to remove. Is there a quicker way than editing each formula?

Formula- If 'refill'
I have two columns of data, column A is a list of numbers and column B holds the word 'refill' at odd intervals ie:

12
14
16
17
13
12 Refill
13
15 Refill

In column C i want a formula such that If 'refill' appears in column B then i get the average of all the numbers in column A since 'refill last appeared in column B.

ok its an easy formula basis, i.e. =if(b1= "refill,avXXXXX,""), but i need the XXX bit, any ideas?

Error: Method 'Range' Of Object '_Global' Failed&quot;
I'm so new to VBA this is probably another simple question, but here goes...

The error-text is:
"Method 'Range' of object '_Global' failed"

It happens at:
rng = Range(gCSchedule.Ws.Cells(gCSchedule.DateRow, StartCol))
See following code.

Mail Merge Error (Method 'Range' Of Object '_Global' Failed)
i'm tring to reuse this code which creates a label on excel. The data used to create the labels is on the "Data" sheet and the labels are generated on the "Label" sheet.

The problem is the first time i run it it generates the labels, then whenever i try again it gives me the following error; Method 'Range' of object '_Global' failed. Each row on the "Data" sheet repreents a label.

Sum Of '£' Values In A Row With '£', '\$' And 'Euro' Values
I am trying to write a formula which can calculate the total value of cells with '£' values in a row with '£', '\$' and 'Euro' values.