Multiple Bonus Options On Sales Tracker
Aug 1, 2013
I am a bit of a novice with excel. I have created my own sales tracker where I get two forms of bonus.
Sheet 1 I have with all my sales. Based on the amount of sales I do I get a set bonus for each amount.
Sheet 2 I have for all the sales that progress.
They also are on a value basis- for every sale I get a certain amount of bonus. I have 2 cells calculating the amount of points. I was wondering if there was a way to have the cells calculate from the bonus table what i would get without me adding it up manually.
Sheet1 is booked leads.H3 calculates the total amount of points.
Sheet 2 is the paid occurences. F2 of that sheet is total points.
Sheet 3 is the bonus structure.
I am looking to put all the information in sheet 1:
Booked Bonus
Occurred Bonus
Total Bonus
Bonus structure is as follows:
Booked Payout Table
Occurred Payout Table
Net Points
Total Bonus
Net Points
Total Bonus
16
GBP 250
[Code] .....
View 4 Replies
ADVERTISEMENT
May 14, 2014
I am looking to take the data off of a "detail" sheet and put it to a summary page. I want the summary page to find the capital and expense from "details" sheet by the month on the "details" sheet. Then for every month add all the expenses and capital and put as 2 values per month, Capital and expense, on the summary page. I am not really sure where to begin but have added my excel workbook that I have started.
View 2 Replies
View Related
Mar 18, 2013
I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")
[Code].....
View 1 Replies
View Related
Jan 5, 2012
I have an excel sheet which has 70 sheets in it...All of them are numbered but not in a sequential manner (eg : 210,211,201,202,215 etc..).The numbering is nothing but the sales order number....
All the sheets have same data (i.e Column A contains Product Sold, Column B contains Sale Value)...The thing is i want to consolidate all the data in sales order number wise. i.e.
Column A Column B
201 $200
202 $300
203 $450
View 8 Replies
View Related
Jul 2, 2009
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.
View 3 Replies
View Related
Feb 4, 2013
I wanted to get the data from data sheet where i have entered the data manually for sales what have done
Now I wanted is when i enter date i should get the product and price in other sheet
Example:
If I enter date in a1 cell
I should get the detail of product in b1-b10(if i sold 10 item )which is in data sheet
And c1 - c10 price (if i sold 10 item @that price)
View 4 Replies
View Related
Feb 20, 2008
See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.
View 2 Replies
View Related
Oct 17, 2013
I have a sales level that I need to track...My rolling 12 months' sales must be $85,000 and my currently monthly sales must be $7,000. I have a sheet that tracks the $85,000 and tells me what I need to achieve that, but I haven't figured out how to include the $7,000 monthly minimum....
The chart below is what I have. So for example, this month it's telling me I only need to sell another 3016.46 to hit the $85,000 rolling 12, but I actually need to hit $4821.79 to meet the $7k minimum.
Actual Rolling 12 Goal
Sep 2012 5,367.24 73,663.30
Oct 2012 5,649.93 69,496.28
Nov 2012 14,163.38 73,451.30 [code]....
View 6 Replies
View Related
Dec 6, 2009
Having great success at using Countif and Sumif in totaling values across two sheets, but at the risk of sounding like a dummy I would like a suggestion as to which formula I should be using for this basic little function;
I have a column on a sheet with dropdowns offering 4 options; Active, Cancelled, Completed, and Suspended. I'm using Countif to total the number of "Active" but I also need to total the number of "Canceled", "Completed", and "Suspended" under the one heading, as if they all meant the same thing.
View 6 Replies
View Related
Apr 23, 2014
I am looking to design a Userform with "OptionButtons" in which the user selects 1 of 4 print formats. I was going to have one single button that stimulates the userform mentioned which allows the user to pick from 4 options. Then there will be a commandbutton1 on userform which allows them to "Print Preview" the selected print style and a commandbutton2 on the userform which allows them to "Print" with the chosen style. The following are the 4 Print Formats:
Print1:
.2 Margins (Top/Bottom/Sides)
Fit all Columns To Page
Rows 1-17 are Normal
From row18 on every row with text in columnA becomes a print break with row16 as a header before it. (the point of this is that row16 is a header but since it will always on the first page and row17 is a text row that would kick it on the next page)
Print2:
.2 Margins (Top/Bottom/Sides)
Fit all Columns To Page
Cells A1:F15 select fit to page and center (This will be the 1st page)
Row16 becomes header for the rest of the sheets. Then each row with text in columnA becomes a print break
Print3:
.2 Margins (Top/Bottom/Sides)
Fit all Columns To Page
Cells A1:F15 select fit to page and center (This will be the 1st page)
Row16 becomes header for the rest of the sheets. Then each row with text in columnA becomes a print break. Hide ColumnE and ColumnF after row15.
Print4:
.2 Margins (Top/Bottom/Sides)
Fit all Columns To Page
Cells A1:F15 select fit to page and center (This will be the 1st page)
Row16 becomes header for the rest of the sheets. Then each row with text in columnA becomes a print break. Hide Column A, B, E, F after row15.
View 1 Replies
View Related
Apr 5, 2007
I have a Case Select statement I just learned how to use yesterday (kind of) What I am wondering is can I have multiple arguements or conditions to each case? For example: my sheet is a "Job Cost Estimate" for construction sales people. The section I am working on is "Fasteners"
I have 2 drop down boxes per line. The first box selects the fastener type, (Screw, Nail, Auger) the next box selects the size in inches and half inches (16 different sizes). Currently the select statement is set up for screws and says if this size then this price. I want to say if this type of fastener and this size then this price.
Additionally, when I select a size in my drop down box, I have to click somewhere else to get it to calculate. Is there a way to select the option and it auto calculates?
Here is my statement in my Worksheet selection change section:
Select Case Range("AZ21").Value ....
View 9 Replies
View Related
Jan 5, 2010
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.
View 4 Replies
View Related
Jan 4, 2005
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 9 Replies
View Related
May 14, 2012
correct below code?
Code:
Private Sub WorkSheet_Change(ByVal target As Range)
Dim rngdv As Range
Dim oldval As String
Dim newval As String
If target.Count > 1 Then GoTo exithandler
On Error Resume Next
Set rngdv = Cells.spcialcells(xlCellTypeAllValidation)
[code].....
i would like to select multiple options from dropdown... but it is not working...
View 4 Replies
View Related
Feb 13, 2013
I have a macro to find me a document from a certain details, but in some circumstances their may be multiple applicable documents, I know nothing of user forms, but how to I find all the documents, show their "modified date", "Name","File type" and select one or which several to open.
My current code:
Code:
Code:
Private Sub OpenPDF()
Dim Ans
Dim TheFile As String
[Code]....
To have something more like a form popup showing for a search within a folder for files containing 'Brians Store':
Open? Name Date
O "Quote Brians Store - ProductsA.xlsm" 1/2/12
O "Quote Brians Store - ProductsB.xlsm" 1/1/12
O "Quote Brians Store - ProductsA.xlsm" 1/5/11
O "Quote Brians Store - ProductsC.xlsm" 1/4/11
And I may want to open the 1st, 2nd and last files.
View 4 Replies
View Related
Apr 2, 2007
Is it possible to update Chart Titles in Batches. For the following Titles:
'Client 1, Jan 07'
'Client 2, Jan 07'
'Client 3, Jan 07'
'Client 4, Jan 07',
Would you be able to change 'Jan 07' to Jan 08' on all of the above whilst keeping the variable client name, in a single batch process?
View 3 Replies
View Related
Mar 8, 2014
I am needing to create a table where one of the values is the mean sales from the last 3 months. Each month is in a different workbook. Each workbook is labeled "1 - Profit & Loss" where 1 is the month up to "12 - Profit & Loss". So each new workbook I create (save as and rename the month) I need it to update the workbooks it looks at.
I also am needed in another cell of the table the same month of the current workbook but exactly one year prior.
I am not at a full year yet, so until there are exactly 13 workbooks (where the 13'th can look back at the 1'st) the second formula should just display "No Comp"
View 2 Replies
View Related
Jun 18, 2009
I need to present dollar values to my boss for a proposed tiered bonus structure and am trying to figure out how to create the necessary formula.
The bonus structure is as follows:
Level 1$8,500 = 3% of fees up to $8,500
Level 2$10,500 = 5% of fees between $8,501 and $10,500
Level 3$15,000 = 7% of fees between $10,500 and $15,000
Tricky part is bonus amounts do not go back to dollar zero. The percentages are only for the stated range.
EX: Level 1 = $255, Level 2 = 99.95, Level 3 = 314.93
Therefore collecting $15,000 would net $669.88
View 3 Replies
View Related
Aug 3, 2012
I need to review a 400,000 row spreadsheet and remove all records where 5 columns are populated with data so I am only eft with records that are missing information.
View 9 Replies
View Related
Oct 1, 2013
I need to build a formula to have one cell display multiple options depending on a value in another box.
If Value is >x and <x display Y
Details:
Cell D5 Holds a dollar amount. E5 is where the calculation will happen. Logically i need it to do the following exactly:
If D5 is between 5000 and 9999, display 75. If D5 is between 10000 and 14999 display 150. If D5 is between 15000 and 19999 display 200. If D5 is >20000 display 250.
View 2 Replies
View Related
Mar 6, 2012
I am trying to write an IF statement. The IF statement should calculate BONUS for an employee GREATER THAN 50 YEARS OLD.
View 4 Replies
View Related
May 8, 2014
There are 5 columns.
Engineer name, attended site(date), report received(date), commercial/heating and bonus(gbp)
Engineers will only get a bonus if they send the reports in 1 day after attended site date for commercial, and 2 days after for heating. If the report is received past this window, they do not get a bonus!
Weekends do not count as a day!
This should be on one row per engineer. I then want to see how much "John Doe" is owed in any given month...
View 3 Replies
View Related
Apr 23, 2013
i am attaching a sheet here.
i have put data validation list in three columns
from data lsts i select unit type , then hinge type then code.... the problem is that as soon as i select code all the values (H W D ) in the next three cells should automatically change.
the values against each cabinet code are also provided in the same sheet.
Sr No.
Unit Type
Unit Type
Code
H
W
D
Qty
1
Base Unit
Double Hinged Door
B60
720
600
580
2
2
Base Unit
Single Hinged Door
B30R
720
300
580
4
View 2 Replies
View Related
Mar 27, 2013
I cannot solve with Excel 2010 and I have searched all over for the answer.
I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.
For example, I would like to turn this:
3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx
Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)
I have a feeling some 'countif' formula would work but I am not sure how to do this.
View 4 Replies
View Related
Oct 3, 2012
In cells A4 to A54, their names are displayed.
In cells I4 to I54, their total sales are displayed.
Starting in cell B59 and C59 I'd like to display their name and sales respectively.
Also in column I I'd have a second value which would be a total of each department. Would it be possible to leave this data out of the formula? No big deal if I have to put that value in the next column.
View 9 Replies
View Related
Mar 29, 2006
I am updating a sports competition ladder. There are two aspects to compiling the table I would like to automate but have been unable to find out how via the excel documentation.
The aspects are.
1. When a team loses by a margin of seven points or less below the total points scored by the winning team then the team is awarded a single "1" bonus point that contributes to their overall competition points tally.
2. When a team scores four tries or more they are likewise awarded a bonus point.
I require formulas that will automatically calculate the bonus points into a cell.
Situation One
cell 1 = Points For (eg. 17)
Cell 2 = Points Against (eg. 24).
In this instance the team did not win. They scored 17 points and the winning team scored 24 points. Therefore the team lost but lost by seven points and is therefore entitled to one (1) bonus point.
SOLUTION NEEDED - I need a formula that will calculate whether cell 2 is seven or less than cell 1, and if so automatically place a total of "1" in cell 3.
Situation 2
Cell 1 = Tries Scored (eg. 5)
In this instance the team has scored five tries. They have scored more than four tries or more and is therefore entitled to one (1) bonus point.
SOLUTION NEEDED - I need a formula that will scan the number in cell 1. If this number is 4 or more, then a total of "1" should be automatically placed in Cell 2.
I have looked into the excel help but to no avail and the search terms are so broad it was a nightmare trying to search the forums.
View 11 Replies
View Related
Jan 15, 2010
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....
View 9 Replies
View Related
Jul 10, 2006
I have a file to calculate bonus based on productivity. The bonus percentage is dependant on productivity percentage and the category of employee. I have given in the below attached file, the range of productivity% and the bonus % for each category. I want to build a formula, to get the bonus % in a seperate table. I tried to do the same using "If" condition, but its not working, as the number of nested IFs are going beyond 7. I have highlighed the column where I want to calculate the formula.
View 5 Replies
View Related
Oct 14, 2009
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.
View 10 Replies
View Related
Mar 21, 2014
Salesman
Sales Amount
Introduced by
sales
introduced
sales
indirectly introduced
sales
Tom
300
none
Tom
300
[Code] ........
I have salespeople who I want to pay a bonus to but I've also offered them a bonus on how much the salesmen they referred to the company sell and this goes like a pyramid down 3 levels.
so I have a list of salesmen next to there name is how much they have sold then who introduced them. from this I need to make a list of each salesman and below all the people he introduced and the people they introduced with the sales in the cell next to them.
To show how it could look I've done an example above of the information I need for tom, as you can see all the data is in the first 3 columns and tom needs to show that he introduced bob & Sue, but because Bob & Sue also introduced someone, tom needs to show he was involved in this as well and jo, terry, mark need to show, preferably in a different column as the bonus for them is lower.
View 5 Replies
View Related
