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
View Complete Thread with Replies
Sponsored Links:
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.
View Replies!
View Related
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?
View Replies!
View Related
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.
View Replies!
View Related
Formula Won't Calculate When Only Some Of The Three Variables Are Input
In column H: =IF(ISERROR(L4J4K4),"",IF(OR(K4="0", K4=""), L4J4, L4J4K4)) 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(L4J4K4),"",IF(K4=""), L4J4, L4J4K4)) and =IF(ISERROR(L4J4K4),"", L4J4K4)) 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.
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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?
View Replies!
View Related
Runtime 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 Runtime 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....
View Replies!
View Related
Runtime 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: Runtime 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.
View Replies!
View Related
Runtime 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 Set oCopyrightNotice = Range("CopyrightNotice") 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).................
View Replies!
View Related
Method 'Add' Of Object 'CommandBarControls' Failed (runtime Error '2147467259')
i get this error on the line in bold Method Add' of object 'CommandBarControls' failed (runtime 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 Sub CreateMenu() 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 _ .Add(Type:=msoControlButton, ID:=1) With myControl...........................
View Replies!
View Related
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?
View Replies!
View Related
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) Nicetohave. If the user enters a lower case letter, then I would like to convert to upper case.
View Replies!
View Related
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?
View Replies!
View Related
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.
View Replies!
View Related
Runtime 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: Runtime 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: ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count) 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...
View Replies!
View Related
"Runtime Error '1004', Method ' Range' Of Object '_Global' Failed"
I keep getting this "Runtime 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.
View Replies!
View Related
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.
View Replies!
View Related
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
View Replies!
View Related
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.
View Replies!
View Related
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?
View Replies!
View Related
'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.
View Replies!
View Related
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
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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 D2B7) so on: B7 = "01Apr2009", C7 = "02Apr2009", 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))),D2B7,IF(AND(ISBLANK(F7),NOT(ISBLANK(E7))),D2E7,IF(AND(ISBLANK(I7),NOT(ISBLANK(H7))),D2H7,IF(AND(ISBLANK(L7),NOT(ISBLANK(K7))),D2K7,"")))))  Excel File is attached.
View Replies!
View Related
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
View Replies!
View Related
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
View Replies!
View Related
'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.)
View Replies!
View Related
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.
View Replies!
View Related
RunTime 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
View Replies!
View Related
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?
View Replies!
View Related
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.
View Replies!
View Related
