# Formula's For Calculate Sales Commissions

Jan 2, 2008
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 9 Replies
ADVERTISEMENT
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
Aug 23, 2013

I have a spreadsheet with detail info for transactions. There are multiple columns...but these are the ones I'm concerned with. ie

Cell A4 has the date range ( 1 month) ie "04/01/2013 - 04/30/2013"

below starts on B5

Cell B - Cell D - Cell I

vendor - Qty - Profit

H20 Month $50 2 7.00

H20 $30 Mo Unl T&T 2 4.20

H20 Month $60 21 88.20

Page Plus Unl $55 3 22.29

PagePlus Unl $39.95 6 32.34

etc...

Cell A32 has the date range ( 1 month) ie "05/01/2013 - 05/312013"

and the vendor, qty and profit like above again....

The above

To the right of this I have :

Cell L2 Cell M2 Cell N2 Cell O2 Cell P2 Cell Q2 Cell R2

Company - April - Topup profit - May Topup Profit - June - Topup profit

H20 29 110.60 71 261.10 93 342.95

PagePlus 19 55.05 25 106.72 14 44.70

etc...

at the bottom, I have the totals for each cell from M2 thru R2.

How can i get L2 thru R2 to sum up the detail amounts on Column I by Column B ?

View 1 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
Jun 9, 2014

I need to write a formula that will look at a number in a cell and compute an amount at 1% up to 55,000. Then anything above the 55,000 compute an amount at 3.25%. Then add the two together.

So a baseline of 55,000 at 1%. Then above the baseline 3.25%.

View 3 Replies
View Related
Jan 28, 2009

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.

View 9 Replies
View Related
Dec 3, 2012

In field A2 I use now() to get the actual date.In colom C8:C1000 I have all monday dates.In colom E8:E1000 I have the weekly sales figures.

How can I calculate the average sales of the last 10 weeks?

View 2 Replies
View Related
Nov 4, 2005

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 9 Replies
View Related
Jun 9, 2014

How to calculate the monthly sales based on the data given ?

View 4 Replies
View Related
Dec 9, 2007

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 9 Replies
View Related
Jun 17, 2014

I'm trying to calculate a running total of ticket sales with 5 different groups (adult, youth, senior day, senior night, and child) to where once the number of tickets are bought it will update automatically. I enter the tickets in cells B5-B9 where it then calculates the amount due and the change due. I need to put the running total in cells B18-B22.

View 3 Replies
View Related
Nov 24, 2013

My company has a catalog of ~6000 unique SKUs that we sell online. Currently we do not have a way to quickly determine how much a given item has sold month over month, and the rate of that change.

I have an excel workbook full of our last year's worth of sales data. I have organized it by placing each month of data in its own worksheet. I would like to be able to create a search box that will allow us to enter in a product ID, and have excel then create a graph with each month's of sales for that particular item. Is that clear?

View 11 Replies
View Related
Aug 20, 2013

Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.

The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.

View 1 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
Aug 6, 2006

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

View 9 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
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
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
Dec 29, 2013

How can I add data to the monthly sales section in the workbook? I can't seem to figure out the code.

mockup.xlsx

View 5 Replies
View Related
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
Jun 1, 2007

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 9 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
Feb 21, 2014

I've a spreadsheet (see attached) which shows sales of 10 different fruits. I've used a LARGE formula (Cells O15:O17) to display the three highest sales and an OFFSET formula (Cells B15:B17) to show what the are.

I only ever want to show the top three sales but I need to take into account instances where more than one fruit has the same sales figure (for example, if lemons and pears both had sales figures of 27, they'd have to be shown along with grapes and strawberries).

Is this possible???

View 8 Replies
View Related
Dec 14, 2012

In my spreadsheet, I have data for each sale in each row. Column C is the buy price, and column D is the sell price. I need column C to be subtracted from column D to get the profit into column E so that I can AutoSUM any cells in column E for total profit over any time period. The problem I'm having is that in columns C and D, I have either a dollar sign in front, or the letter m following without a space (ie $45.00 or 150m). Many cells in column C also contain another word following the buy price, and many cells in column D have a date following the sell price (ie $32.00 12/11 or 42m buyout).

Examples of how my spreadsheet looks:

A1: 12/13/12 B1: Item #1 C1: 100m D1: 152m

A2: 12/14/12 B2: Item #2 C2: $20.00 D2: $35.00

A3: 12/15/12 B3: Item #3 C3: 90m bid D3: 120m 12/16

So looking at my example above, I would need cell E1 to automatically show "52m", cell E2 "$15.00", and cell E3 "30m"

How I can get a formula for all of column E that will give me my profit for both $ and "m" for each item (row). I have tried the SUM function but just keep getting the #VALUE! error, it does not ignore the text like many people say it should.

View 6 Replies
View Related
Mar 18, 2014

I'm trying to create a report that will include current month and previous month by territory. Unfortunately, i'm getting the same values for both months.

PriorMonth=CALCULATE([Sum of TotalDue]),DATEADD(Date[Date],-1,month))

Date =Date table ( Date, Month, year)

Relationship= Date.Date=InternetSale.OrderDate

Current Month=Sum([Amount])

View 5 Replies
View Related
Apr 4, 2014

I am trying to use sumif formula to add sales forecast based on three conditions but i also want to add the revenue for current month which i have but for the next one months as well as two months plus.. this will change based on the current month.. below is what I am using for the current month..

=IF($B$3=Reference_Data!E2,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),

IF($B$3=Reference_Data!E3,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),

IF($B$3=Reference_Data!E4,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),

[Code] ...........

View 2 Replies
View Related
Aug 5, 2008

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 9 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 27, 2014

I am writing a formula that will sum data for a 12 month period that is not based on a calendar year. For example may have data starting in June 2011 running to date, but not every month. If I set a start date for Year 1, say April 2012, I need to see Year 0 numbers (anything prior to April 2012), Year 1 numbers (April 2012 to March 2013), Year 2 numbers (April 2013 to March 2014), and so on. Date format is currently mmm-yy.

If this could be performed via a macro which references the database (spreadsheet), then even better. The database will contain multiple columns, of which two will contain the date and corresponding number respectively.

View 12 Replies
View Related
May 21, 2009

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 9 Replies
View Related
Oct 7, 2009

i need a formula for which calculate all cell (calculating years months and days formula). FIND ATTACHED .xls file i need a answer in cell highlited in yellow color.

View 3 Replies
View Related
May 6, 2013

Formula to calculate GST? I have a Non-capital and a Capital column, then a Claimable GST column.

I currently have '=SUM(E5/11)' in the Claimable GST column (G5).

If E5 is a zero value I need the sum of F5/11 in G5.

View 2 Replies
View Related
Feb 22, 2014

I need to calculate a TAT formula for the below case

P1 -- 1 Hour

P2 -- 2 Hour

P3 -- 4 Hour

P4 -- 8 Hour

Shift Start time : 6:30pm

Shift End Time : 3:30am

Suppose a request comes at 9:30pm and it is Priority 1 (P1) then it should be completed at 9:30pm + 1 hour = 10:30pm. I have given the below formula to execute this

=IF(A2="P1",1,IF(A2="P2",2,IF(A2="P3",4,IF(A2="P4",8,0))))

=B2+(TIME(D2,0,0))

The problem here is if the request is P4 then its 8 hr TAT and the formula will calculate the time as 5:30am. Since the shift time end at 3:30am itself the actual TAT should be the next day 8:30pm.

formula to calculate the TAT which includes the start time and end time and also excludes the weekends and holidays.

View 14 Replies
View Related
Nov 2, 2009

I use this formula to calculate a %, but naturally when there is a 0 excel returns the #DIV/0! error. Can someone fix my formula so the error gets replaced with a 0

in the formula the 0 would be in cell W2 =IF(N2="yes",BN2/W2,0.5).

View 3 Replies
View Related
Dec 23, 2008

i just started with a company Royal Mail and they have some very strange timings for starting and finishing and i need a formula to help me work my money out and my hours to the precise min.

for example i have a shift comming up this week and my start time is down for 22:45 and my calculated finishing time is 07:23. i need a formula that will show on a spread sheet my exact time of work.

the other time i had was 22:35 to 06:35...it all seems prity simple to alot of people but this is my other problem. if i work from 06 - 18:00 i get one rate and if i work from 18 - 06:00 i get another rate.

so i need to know how to calculate for the 35 min at say 15.24 ph.

View 14 Replies
View Related
Nov 25, 2006

Working from cell D19 I am trying to find a formula to calculate how much tax to pay.

I have a TAXABLE INCOME figure (in cell D18) , from this figure

The FIRST £1520 should be multiplied by 0.1 (in cell C19,)

The next £26880 should be multiplied by 0.22 (in cell C20, the answer to be put into D20)

Any remaining from the Taxable Income figure (put into cell C21), should be multiplied by 0.4 (the answer placed in cell D21)

View 9 Replies
View Related
Jun 20, 2006

i am trying to build a formula to calculate a persons age.

I have tried using the DATEIF function but i keep getting a N/A

View 9 Replies
View Related