Formula Structure: The Total Fees (H2) Is A Part Of The Calculation
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:
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.
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‎
Since the sales and bonus figures are subject to change each year I'd like the formula to point to the numbers but that part seems easy if I can get the formula right.
I am trying to write a calc for a total an amount based on a list choice Here is the Set up Fields A1 thru A255 are number 1 to 255 Fields B1 thru B255 are number 255 to 1 Field C1 is a List box set using A1 to A255 and its list Field D1 is set as the needed Calculation
When you choose a number in C1 the D1 calculation should total the amount of Fields B* to B1, * being the corrisponding B field to the A field from the list
Example C1=100 (Field A100) D1=Sum of Fields B100 to B1
I have a INDENTED list from a cad software that looks like this. It gives me the quantaty of each position. For instant in the list
I have 5 of 1.1 but because 1.1 is part of 1 makes my total 3x5=15 I have 2 of 1.1.1 but because 1.1.1 is part of 1.1 which again is part of 1 makes the total 2x5x3=30
I am attempting to create a time chart that keep track how long a particular person works on an individual project to finally calculate profit & loss. Attached is the spreadsheet.
I am using invoice #'s to differentiate the jobs but have no clue how to make it calculate how much time was spent per job, per person then calculate the total cost per job per person?
sheets.time chart.xlsx
2014 tab- grey column is installer and white is shop employees profit loss tab- all calculations done here payroll tab- all cost data here
I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.
Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.
I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.
number for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.
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.
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.
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.
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.
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.
I have a very large spreadsheet which holds a lot of data, and has a custom reports system built into it, (i.e. running on a load of macro's)... The reports gather their info from a range of hidden cells which run different formula's to provide such results as 1 or 0 so that it collates into another sheet...
however, I have found recently that when I need to update any of the formula's and whatnot, the formula does the calculation and then disappears... whereas I need the formula to be there constantly so that the report is providing the correct information!
I am trying to create my household budget in Excel, but there is a sub-category.
I need a formula which will calculate the following:
Under the "Daily living" section, I have typed the names of the sub-categories, eg: , Groceries General Household Cleaning Education/Lessons Cash
I want it to find all totals next to the word "groceries" in the main part of the spreadsheet, where I type in my credit card statements according to date.
In other words, I want it to add up all my entries/totals next to the word 'groceries' and show the total up at the top of the document, in the "amount" column so I can see the monthly category totals at a glance.
I need a cell formula (gets put in by macro) that returns part of a workbook name.
The worksheet name will always be different, but will contain either SD1, SD4, BDO, BD (not neccessarily in Caps) and it is this key code that i want returned in the cell (G2).
so if the worksheet name is
NW SD1 bobs your uncle. when macro runs G2 returns SD1 or BDO xyz. when macro runs G2 returns BDO
This IF formula does not work on the last part of this formula IF(E10>50,"$50.00",0)))) and I think there is a conflict with the IF(E9>199,(E9*0.01) but I cannot figure out what the problem is.
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
I know the Dictionary Object is confined to the Microsoft Word Object structure though can it be used in Excel. If not, is there a suitable replacement.
