IF Statement- Spreadsheet To Calculate Referral Fees Paid To Our Agents
Jan 8, 2004
I am trying to build a spreadsheet to calculate referral fees paid to our agents. These are sliding scale fees and I need the formula to consider both the high and low range of the project value. For example we pay a certain percentages for sales in the following ranges:
Up to $5,000,000 (I got this one to work)
$5,000,001 to $7,500,000
$7,500,001 to $10,000,000
$10,000,000+
I assume the best way to do this is with an IF command but I only know how to use it for a single value, not a range. Also, if the statement is not true, then return $0.
View 9 Replies
ADVERTISEMENT
May 29, 2008
I am trying to automate something I've been doing manually. Let me explain.
1.) I am givien information about fees associated with a mutual fund product. A sample is reproduced in F4:F8.
2.) Next, this information is entered into the table. See A4:D12.
3.) Finally, from this table, I must calculate the fees associated with various account sizes.
4.) I did this manually in cells G15:G16 for 2 different account sizes.
View 14 Replies
View Related
Nov 15, 2013
I am looking to setup a formula to calculate a Fee that is based on the number of users of a service in a month, with the rate varying by the number of users. The first million users in a month will bring in $0.40 per user, for users 1-4M it will bring in $0.30 per user, for users 4M-7M it will bring in .20 per user, and for users beyond 7 million it will be .10 per user. So for example, if a user count was 9 million in a month, the calculation would be ($0.40 x 1 million) + ($0.30 x 3 million) + ($0.20 x 3 million) + ($0.10 x 2 million). I know the answer to the problem is obviously $2,100,000 but I can't build the formula that solves that and can handle instances where the user count is capped in one of the individual brackets. (IE if there are 3.5M users)
I've attached an example spreadsheet : Variable Fee Schedule.xlsx‎
View 7 Replies
View Related
Feb 26, 2014
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.
View 5 Replies
View Related
Jul 13, 2009
Part of the assesment task is to write a formula, to work out how many days in advance the customer paid, and then apply the needed discount. I have tried several basica variations to the formula, and keep getting the same Err message.
give point me in the right direction to how i can calculate amount of days paid in advance and apply a % discount?
attached is the start of the assesment question.
You should create and enter formulas to calculate the No. of Days paid in Advance, the Discount and the Course Fee Paid.
Use a VLOOKUP function in your template to determine the discount rate to be used for the calculation of the Discount. Your template should include a separate discount table containing the following information about the discount received:
• If students pay the course fee less than 7 days prior to the course commencing then they receive no discount.
• If students pay the course fee 7 to 13 days prior to the course commencing then they receive a discount of 5%.
• If students pay the course fee 14 to 20 days prior to the course commencing then they receive a discount of 8%.
• If students pay the course fee 21 days or more prior to the course commencing then they receive a discount of 10%.
View 6 Replies
View Related
Jan 13, 2010
attached file which contains about (150) agents sheets,i need a way which will move me to the desired sheet by pressing its name from the list in the main one , i need this because i shall hide all the sheets.
View 14 Replies
View Related
Jan 1, 2006
I am creating a data tracking workbook. Information is entered into cells on
different sheets, then the tallies are pulled to one easier to read sheet.
In creating the tally sheet, I am setting the cell formulas. The cells
referred to are constant, only the sheet referred to changes. For example,
the cell might read
='FP 1'!$I$25
and the next one should read
='FP 2'!$I$25
....and so on. I would like to be able to paste the formula and have the
referred to sheet change so that I do not have to go through and make
hundreds of little changes.
View 10 Replies
View Related
Mar 31, 2014
I wish to create a spreadsheet which has formulas which would enable me to see if a patient has had more than one referral created within the next 30-90 days [the actual number of days not important - the ability to modify the formula to reflect which time period required would be cool] and also to see if a patient has been referred for the same medical condition again. Thus in my mind at least two different formulas - has the patient been referred again within a time period of days and has the patient been referred again for the same medical condition.
Attached is an example referral spreadsheet I have created
View 4 Replies
View Related
Feb 13, 2007
I am trying to use a combination of dependent named ranges and variable length ranges, so one can select in eg Col A truck name from a drop down list, and then in Col B, the engine variant only for that make of truck. The indirect(substitute) function works well if I define the dependent range name (the engine variant) using actual cell refs. However if I use the variable length name definition as described in Ozgrid (ie with offset function), I get an error message and the dropdown menu freezes. Is there anyway to overcome this without resorting to VBA?
View 4 Replies
View Related
Mar 24, 2009
I have a variety of products that i sell out to different agents at various levels of commissions. These range from a possible earning for the agents from 12%-30%. Is there a set formula that will take my initial buying price ie £100 and i wish to earn £5 regardless of whether the agent earns 12% or 30%. so an agent on 12% would buy from me at £119.32 and an agent on 30% would buy from me at £150
View 3 Replies
View Related
Apr 21, 2014
Is there a way to accurately compute a conversion report that has agents with multiple roles? Right now it is done this way:
Agents are skilled to two skills, sales & service. They will make most of the bookings on sales (few bookings on service). I want a report that will calculate the agents who are on sales/service bookings vs calls while persons on service/sales bookings vs calls.
Right now, because the service/sales agents get way less sales calls, you will find that their overal conversion is way higher than an agent who is a sales/service agent. What this does is drive the overall conversion rate so high that it always favour the agents on service/sales, a sales/service agent is always disadvantaged.
Is there a way in excel where i can more accurately calculate this while maintaining some degree of parity?
See report attached
I have highlighted some of the SE/SA agents that are way high
Note: SE/SA = Service/Sales SA/SE = Sales/Service
View 2 Replies
View Related
May 17, 2013
I am looking for a formula or VBA code that will allow me to Select a Team leaders name and all the agents reporting to that team leaders would populate. I have the first part of that problem solved but its getting the agents names to generate uniquely that I find problematic.
Below is a step by step example of how i intend it to work
Step 1
I select the Teamleaders name
Shelaine
Once that has populated the Team Names automatically appear
Shelaine
Score
Andrew
20%
John
30%
Mark
40%
Sarah
50%
June
60%
The scores will populate on its own as I will include a Vlookup formula to generate the scores, Its just the Agents names. I am using Excel 2007
View 2 Replies
View Related
Feb 7, 2008
I have two spreadsheets, i suppose one part of the solution may be to copy spreadsheet 2 into a new worksheet in spreadsheet 1, but this is my current situation:
Spreadsheet 1 has "Employee lastname" in column F, "Employee Firstname" in column G. Column A in this sheet needs updating with the data populated in column H for that employee in spreadsheet 2 - the lastname is contained in column B, firstname in column C - so i need to to a match.
View 12 Replies
View Related
Nov 8, 2006
I need to know how to calculate totals from a spreadsheet in conjunction with an IF statement.
I have a columns A & B below, i would like to calculate the numbers in coulmn B only if they match a specific criteria in column A.
For example: I want calculate the total of the numbers in coulmn 'B' only if column A is a '1'.
AB
24
33
12
21
35
13
22
View 10 Replies
View Related
Dec 7, 2009
I have a situation where an asset-based fee charged to a client will be reduced (20% discount -
e.g., pay 80% of the fee) if below a set amount (250000000,(+B6-250000000)*0.00075+287500,IF(B6>100000000,(B6-100000000)*0.001+137500,IF(B6>50000000,(+B6-50000000)*0.00125+75000,+B6*0.0015))))>24000,IF(,(+B6-500000000)*0.0005+475000,IF(B6>250000000,(+B6-250000000)*0.00075+287500,IF(B6>100000000,(B6-100000000)*0.001+137500,IF(B6>50000000,(+B6-50000000)*0.00125+75000,+B6*0.0015)))),24000)*0.8
How can I rewrite this formula to capture.
View 9 Replies
View Related
Nov 2, 2009
See the attached spreadsheet and the comment boxes inserted containing my questions.
View 10 Replies
View Related
Feb 9, 2010
I am an office worker in a (very) small business, and so we use excel as a simple database, which covers our needs easily. In particular, we use a sheet as a register of fees. The question I have involves the creation of a macro to find over-due fees.
The format of the sheet is:
A.Invoice date | B.Reference code | C.Client name | D.$$ cost | E.Due date (always 14 days from invoice date) | F.Payment received date | G.$$ Received
At current, when a fee is overdue, we manually check the list for unpaid fees past the due date and highlight them (generally yellow) and then copy and paste these to a seperate sheet (same book). I believe this could easily be accomplished with a macro, but I am not at all fluent in the coding.
What I believe the macro would require to do is sort through the list (first invoice in row 4), Check ifblank for column B (to make sure a fee exists), then check current date against due date. If 'overdue', check that G=D (received = cost). If not, highlight that row and copy to row 1 of new sheet. then return to fee sheet, move down a row, rinse and repeat untill out of fee's.
If column B is blank, then it might be the 2 row gap left between each month, so it would need to check ifblank for 3 consecutive rows before ending the macro.
If due date is blank (which happens sometimes because we enter drafts into the sheet, and just leave the dates blank until we send them out, but still has a ref code) then it should count as 'not overdue' and move on.
I think it'd be better as an on-click macro rather then an automatic macro, since it only needs to be done at certain times, not every time we fiddle with the sheet a bit. Being on-click, it could even use the same sheet to copy all the overdue's to (rather then create a new sheet every time), as long as it 'select all - delete's it before copying the new things.
Thanks for any help. I signed up just to get this help, so I deeply appologize if I have broken some sort of policy or rule.
View 8 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
Dec 1, 2008
I am using Excel 2007. I want to create a graduating schedule to figure fees. For example, $10 per thousand for the first $500,000. $13 for the next $2,000,000, etc. I have tried to understand the IF function but am not sure if this is the correct one to use.
View 9 Replies
View Related
Apr 22, 2006
I am attempting to calculate commission (J2) based on the data entered in cell D2
1. The total fees (H2) is a part of the calculation. It represents a value from .5% to a maximum of 3%.
2. If the Loan Description is specifically ‘80/20’ then 80% of the Loan amount is used in calculating the commission. (note: ‘80/20*’ is also a valid entry). Otherwise the total loan amount is used.
D E F G H I J
Loan Dsc. Int. DSCNT% Y-S-P% Fees Loan Amount Commission
80/20 7.52.0 1.0 3.0 $137,403 $4,122.09
I attempted this formula and obviously it’s incorrect:
Calculating Commission (J2)
=IF(H2=" "," ",=IF(D2="80/20",(I2*0.80)*(H2*0.01),I2 * (H2*0.01))
1. If H2 is blank then TRUE enter a blank
2. FALSE: H2 contains a fee rate then calculate the commission Commission Calculation: If the Loan Description is 80/20 then take 80% of the loan amount and multiply it by the rate fee amount (as a percentage) to get the commission. If the Loan Description is NOT 80/20 then use the whole loan amount in the calculation.
View 3 Replies
View Related
Jan 3, 2010
I have a blank workbook that I am just about ready to deploy, but it is very slow to calculate. I know I can manually calculate, but I would rather the workbook just be fast and calculate on its own. The workbook is blank now, but will be used all of 2010 and will ultimately end up being around 20,000 lines. So, I have built in the formulas and conditional formatting into the 20,000 lines now so I don’t have to continually add lines.
So, the questions are…
What part of the workbook is slowing it down?
Here is how the tabs breakdown:
NOTESJust text no formulas
DATA20,000 Rows
1 formula of conditional formatting (Covers entire 20,000 rows and all columns except A)
4 columns with formulas (2 using VLOOKUP, 1 using SUMIF, 1 simple addition)
REPORT2 pivot tables linking to DATA
View 9 Replies
View Related
Jul 5, 2014
I need some different columns within a row to calculate based on a statement found in an earlier column in the row. something similar to below. there will only be two statements total which are noted on the table below.
A
B
C
D
E
1
0
0
20
5%
[Code].....
So basically If column A = disposition calculate D and E in column B but if Column A = Receipt calculate in column C
The formula for D and E is not exactly as shown just using that as a quick example. I'm using office 2010.
View 1 Replies
View Related
Jan 1, 2009
Writing a single nested IF statement function to calculate the grades of the students the following rules:
If the student grade isHis/her letter grade is
Greater or equal to 90:A
Greater or equal to 80 and less then 90:B
Greater or equal to 70 and less then 80:C
Greater or equal to 60 and less then 70:D
Any other grade:F
View 5 Replies
View Related
Aug 1, 2014
I am using the following formula to calculate due dates based on the rank i assign an issue.
Note: a similar formula is used for the due date of section one, but if it is not complete, i do not want this formula to kick in a determine the due date for section 2. The formula itself is working fine, but is giving a 'due date' of 1/30/1900. I was wondering if there is a way to not display anything if it has no number to calculate from.
=IF(ISNUMBER(SEARCH("S",D11)),F11+30,IF(ISNUMBER(SEARCH("B",D11)),F11+30,
IF(ISNUMBER(SEARCH("A",D11)),F11+30,IF(ISNUMBER(SEARCH("QNM",D11)),F11+30,""))))
View 4 Replies
View Related
Jun 29, 2009
I have around 250 Employees Camp Meals Statements. Each day we prepare a Excell Sheet and enter the details file attached for easy reference Im manually calculating the Totals in each sheet if emp takes meals we marked as Y otherwise N based on that i want the total meals daily. One more thing Base on employeed code i want the monthly statement in another sheet same file attached..
View 9 Replies
View Related
Sep 11, 2013
I am putting together a procedure to calculate a value based on all of the active cells in column "A" and a couple other conditions. "k" is my variable to put a value into (k,8) until all the active cells in column A have a corresponding value in (k,8)
I have my scenarios all built out into If statements and the code runs....but no value is being added to cell (k,8) for any of the rows with entries in column "A" when the procedure is finished running. Depending on the values in (k,2) and (k,3) there are four different ways the value in (k,8) needs to calculate as I have built out below. Why my values aren't being added to (k,8)?
Code:
Sub CalcSpecialFee()
Dim k As Integer
Dim cell As Range
k = 4
Application.ScreenUpdating = False
Sheets("Summary").Select
[Code] .........
View 1 Replies
View Related
Feb 3, 2014
I don't want to appear too expensive or give my products away. If I have a part that costs me £20.00 plus vat i.e £24.00. I have to include carriage £5.00 plus vat i.e £6.00. Ebay fees for car parts including PayPal is 10%. How much would I charge to retain 20% percent after all fees and charges and what formula would I use to make the calculator.
View 2 Replies
View Related
Nov 16, 2011
I am trying to set up a spreadsheet to calculate margin on stock positions.
There are 3 tiers, based on number of shares. My example:
400 shares in held in total.
1st 200 shs margin at 10%.
next 100 shs margin at 20%.
everything above 300 shs (ie 100) margin at 30%.
The 400 shs may be made up in any number of ways, (ie lots of smaller amounts) and i need a formula to give me the margin amount on each position, flexible enough for when positions are added / removed.
I've been looking at SUMPRODUCT but can only seem to make it work for the total, not each individual position.
View 9 Replies
View Related
Oct 13, 2009
Need formula to calculate the average %age in the attached spreadsheet. I would like to enter a score between 1 and 4, but with 1 = 10%, 2 = 25%, 3 = 80% and 4 = 100%. The score in the cell must still show as between 1 and 4 but the total must be an average of the relevant %ages. i.e. if scores are recorded as 1, 2, 3, 4, then the total average % will be (10%+25%+80%+100%)/4 = 53.75%. I'm not sure whether this should be in the Validation or in the Total cell.
View 2 Replies
View Related
Aug 7, 2008
I am trying to create a spreadsheet to calculate the macro nutrient ratio of foods based on carbohydrates, proteins, and fats and how many units/servings I eat of each. I've attached the file I've started. It's fairly straightforward and simple, thus far.
I would like to be able to associate a food with its various nutritional data in its row (fat, carbs, sugar, fiber, sat. fats, protein.. etc.). With all of my food items entered, I would like to be able to choose from a list, have it prompt me for how many units I've eaten then plug it into my formula to calculate totals and ratios.. The final flow should go soemthing like this...
1. Select a food from a list (each food on the list has its associated characteristics)..
2. Select how much ( each food will have an associated serving size i.e. 1/2 cup, 1oz..) ---- (4) oz VS. entering each unit in A1
3. Make these totals of each characteristic feed into my final chart with which to track and graph.
View 4 Replies
View Related
