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%.
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
I'd like to know how I go about to key a formula to compute the due date 30days after the invoice date and How to key a formula to determine hours worked.
View 14 Replies View RelatedI am currently trying to create an excel spreadsheet where I would know how much interest I will pay in the next six months, since the balance changes every month I am not sure how to formulate the total interest paid with in a specified time period.
I currently can calculate the interest and fill in the below rows with each month but I would like to simplify the report so that I can add the Balance, Rate, Payment(2% of balance), and Time period and in another cell it will tell me how much interest I will pay in that time period. Bascially I would like to keep all the calculations in one row.
For example
Balance | Rate | Payment | Time Period | Interest Paid
$1,000 | 10.00%|$20.00 | 6 | $48.56
I have some question regarding elapse time using excel formula.. Just want to compute for the total hours consume by a person per task assign base on 5 days,8 hours per day working schedule(Saturday and sunday is not included since it is the restday of the agent. Working hours is 8am to 5pm. 12pm -1pm is the time agents take there lunch.
See below example and expected Result:
AGENTSStart timeEnd timeExpected Result(hour)RemarksCHARICE5/14/12 8:00 AM5/14/12 3:00 PM6.00Result subracted by 1 1hr breakALICE5/15/12 8:00 AM5/17/12 3:00 PM22.00Result subracted by 3 1hr breakJAKE5/16/12 8:00 AM5/21/12 3:00 PM30.00Result subracted by 3 1hr break(Saturday and Sunday is not included(may19-20))JOHNNY5/14/12 8:00 AM5/17/12 3:00 PM30.00Result minus 4 1hr break
Is it possible to show "0" zero in the total average column without inputing zeros in the blank cells in row B3:E3 & B4:E4? There are months we receive no boat & RV orders, so those months wll be zero most of the time. Instead of having #DIV/0! it show 0, without having to input zero in cells to compute the average.
A
B
C
D
E
F
1
Jan
Feb
Mar
April
Total Average
[Code] .......
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.
.............................24............30............36
300014999..........9.00%.......11.00%.....12.00%
1500099999........9.50%.......11.50%.....12.50%
100000249999.....9.00%......11.00%.....13.00%
I need an all inclusive formula (all in one cell) that would look at the above table and depending upon the price paid (3000-14999 or 15000-99999 or 100000-249999) and depending upon what monthly term they choose (24, 30, or 36), the appropriate finance charge would be used to calculate a total cost (9-13%). The only way I know to do this is by using IF's and AND's, but there are simply too many arguments and I cannot properly write the formula.
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
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 ?
I have two excel sheets. The first lists all orders submitted. The second lists all orders paid, or pending payment. I would like a column in the first sheet that would show if that order was paid, by pulling the info from the second sheet.
View 14 Replies View RelatedI am trying to sum rows based on unusual criteria: i.e. A table of date c2:t9 contains details of expenditure for 16 periods for insurance and motor expenses. i want to convert this to a cashflow format and reflect when the item is actually paid, so if it is paid in february, march, etc (given in the original table) I want to add the totals in the months preceding the set date. also, if the criteria is a number <12 then i want to add up the preceding amounts if the period number is a factor of that number e.g - criteria is 3, then i want to add rows 1-3 and put in period 3, 4-6 and put in period 6.
View 6 Replies View Relatedi was assigned to make a computation for our individual scores using this:
EMPLOYEES A-N
inputs:
SURVEY(0-100)
LH(lost hour)
AHT(average handling time)
compute for:
SCORE
RANK
the SCORE has a total of 100%
so to get the SCORE, each input gets a percentage: SURVEY=40% LH=30% AHT=30% then added.
but the thing is LH and AHT has an equivalent.
LH
0 to 1=100
1.1 to 2=90
2.1 to 3=80
3.1 to 4=70
4.1 to 5=60
5.1 above=20......
eXAMPLE:
EMPLOYEE A
SURVEY(0-100) = 33.00
LH(lost hour) = 12.00
AHT(average handling time) = 14.20
SCORE = CODE
RANK = CODE
now for the rank i just use the code =RANK(SCORE A, SCORE A:SCORE N)
im lost on how to compute the SCORE though coz i can just use =((SURVEY*40%)+(LH*30%)+(AHT*30%))
but the one that needs to be on the LH and AHT are the equivalents and not the inputs.
and there is 1st 2nd and 3rd place. the name of the top 3 employees should be shown under the table.
I want to create a spreadsheet that I can export my transactions from my credit card onto -- is there a way to make it so that the transactions that haven't been covered by my most recent payment(s) are red, while the ones that are paid are green without manually going through & doing it? I know there's the IF, TRUE, FALSE formulas, but I'm confused on how to use them.
Basically, if I spend $1,000 between 5 transactions and make a $400 payment, I want the oldest transactions totalling up to $400 to turn green, while the remaining are stay red until a new payment is posted.
I have this code which need to capture Rank & URLS of given keywords in Google paid search but now its not capturing any rank & urls, its gives no data found. solve this and modify it in such way that it record the position of the paid advert on the google page, advert title and description and then click on the ad to capture the actual landing page (not the landing page url shown in the paid ad).
View 3 Replies View RelatedI'm trying to mark up in advance the dates where i'll get paid. I get paid monthly on the 2nd last working weekday of the month. So because we have the 30/11/2009 falling on Monday of next week, my pay day for this month is Friday 28/11/2009.in December it should be the 30/12/2009.
I'm getting all tied up in Networkdays, EOm and weekday function!
I've attached a Screen Shot of the of 2 Workbook, named Installation Tailgate 2007v1.xlsm and new workbook and i've also attached the excel program itseff in zip.
Installation Tailgate 2007v1 workbook, have a 13 column and nth number of rows ( and will keep growing)
columns D & E are hidden
columns G, I, K, M are checkboxes
if the user clicks on the command button it will copy and paste the selected cells on new workbook
unchecked cells on Installation Tailgate 2007v1 workbook should have a "0.00" on Child workbook.
then compute the Sum of Columns C, D, E, F, G, the total should be on last of their row
I have 15*2,14*2,14.5 in cell A1 and would like this to be computed and its result (i.e 72.5) displayed in cell A2. See below for clearer picture:
A1 A2
15*2,14*2,14.5 72.5
I have the following macro to compute data using SUMMIFS
Range("E" & finalrow + 9).Formula = "=SUM(SUMIFS(E8:E" & finalrow & ",F8:F" & finalrow & ",{""PSP101"",""PSP611"",""PSP140",""7*""}))"
The last line up code is mot working as I am tying to using a Wildcard for the last criteria
""7*""
Where text starting with a 7 appears in column F for eg 76601G, 75106K etc appears it must be included in the criteria
Full Code-below
Sub Totals()
finalrow = Range("A65536").End(xlUp).Row
Range("A" & finalrow + 3).Value = "Total Movement"
Range("E" & finalrow + 3).Formula = "=sum(E8:E" & finalrow & ")"
[Code]....
I've been having a hard time with this i need a formula which will compute a value based on a multiple cell.
I have a cell (AQ) it contains the number of days a patient is in a center i need to countif the cell value is >=90 if it is it will then calculate the values in cell (AV) which is >- 1.4 and get the average of cells in AV.
Find attached , formula on d2 and e2 , raw data sheet1
Attached Files : counting seller paid.xls
In excel 2007 i need to do a formula...
I have the number of hours and de couste...in an other cell i have the expression that says that is paid or not....
I need only sum the cells that have the expression paid ...how do i can do this?...
I have an issue on manipulating the data in my worksheet below. I need to update column 'remark' when the customer has pay fully. Example customer 1003 and 1004 both pay completely so I need to update done in column remark. For customer 1002, because not fully pay, so cannot update done.
customer inv no invoice payment remark
1002 A12 100 100
1002 B45 120 0
1003 W12 90 90 done
1004 F12 30 30 done
1004 F17 45 45 done
May I know what function or coding can achieve this?
Most Recent Paid EMI Date:
I need to get the Most Recently paid EMI in the List it should be in tune with the TODAY() as well as the PAID one indicated by "X" in Column F..
Next EMI DATE:
This would be the NEXT EMI date as the name suggests after the Most recent paid EMI…In this case 14-Feb-09
Date to Replenish:
This date would be when the Amount in the Account has got reduced indicated by "X" in column E...In this case 10-May-09.
I'm trying to make a macro that match table in sheet "Vocabulary" to the second table (columns Q,R,S,T) in sheet "Overview" and compute correct values in column U using weights from column F, sheet Vocabulary on values from a corresponding cell at column M, sheet Overview. The entries for every ISIN in the sheet Vocabulary are in no particular order and some positions from sheet Overview don't have to exist in Vocabulary at all (but I still need them to display in the table on the right to be able to make charts from summing these categories).
Portfolios.zip
How should the end result look like is displayed on the last sheet.
using the ttest formula function
View 2 Replies View Relatedi m making a very small calculator. It's a little hard to describe, but I need to look like the following
Let's say the individual got here on 1 Jan
Phase 1 is for days 1-14 last for 14 days
Phase 2 is for Day 15-35 last for 21 days
Phase 3 is day 36+
This is what I need it to look like: All the phase information will compute automatically after inputting the date arrived. In addition I would also like to be able to change one of the phase dates and the remainder of the phases compensate according to the newly entered date.
Date Arrived = 01 Jan 09
Phase 1 = 01jan09
Phase 2 = 04Feb09
Phase 3 = 05 Feb09
how to compute for staffing requirement with turnaround time?
View 2 Replies View RelatedI work for a company that owns hotels and casinos all over the States.
Company wide I would say we have about 13,000 employees. The hotel I work at
has around 125 employees (even w/ the constant turnover after Katrina hit).
My problem is, that my corporate office refuses to update our payroll
operations. We still use punch time cards that every other Friday I have to
manually enter time into the payroll server timesheet. Our system does not
calculate paid time off, among a lot of other things. My employees come to
me almost daily asking if I can tell them how many PTO days they have left.
The only way I have to figure it out is by going through every PTO bi-weekly
request form spreadsheet until I have added up the days taken throughout
their service year.
I am trying to build a spreadsheet in Excel 2003, that consists of:
Emp#, Name, Job Class, Hourly Rate, Hire Date, Birthdate, Last Review Date,
PTO per yr, PTO taken, PTO days left....among other personnel information.
I want to create a formula that will calculate the PTO per yr the employee
has.
After 6 months = 2 days
1 year = 10 days
2 years = 14 days
6 years = 19 days
16 years = 25 days
26 years = 30 days
(If the employee does not use the days within their service year, they loose
it.)
I also want in the PTO taken column a formula that will pull the # of days
taken from the PTO request spreadsheet that I have to send in to home office.
The employee's differ every spreadsheet, as does their placement. Once the
days taken are in their proper column, I want the PTO left to be calculated
from PTO days per year minus the days taken.
How to compute the realised Profit or Loss for each of the shares transacted for the year ended 31 December 2008 based on average cost of each share.
using AVERAGEIF, if appropriate. [Pls refer to the attachment.]