Calculate Sliding Scale Calculation

I need to be able to calculate a commission as follows:

Total Amount=$500
Total Commission (0-$100, 5%)+($101-399, 10%)+(>$400, 15%)

This would calculate a different percentage for each of the ranges
above and then add them together.  I hope I'm making myself clear
because I'm confused just writing this.

It seems like there should be a very easy way to do this in Excel.

Any assistance would be most appreciated.

Thanks,
Andy

0
3/13/2007 5:53:38 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
1059 Views

Similar Articles

[PageSpeed] 18

See

    http://www.mcgimpsey.com/excel/variablerate.html

In article <1173808418.215657.199740@v33g2000cwv.googlegroups.com>,
 "Andy" <andrewcmcgibbon@gmail.com> wrote:

> I need to be able to calculate a commission as follows:
> 
> Total Amount=$500
> Total Commission (0-$100, 5%)+($101-399, 10%)+(>$400, 15%)
> 
> This would calculate a different percentage for each of the ranges
> above and then add them together.  I hope I'm making myself clear
> because I'm confused just writing this.
> 
> It seems like there should be a very easy way to do this in Excel.
> 
> Any assistance would be most appreciated.
> 
> Thanks,
> Andy
0
jemcgimpsey (6723)
3/13/2007 6:02:12 PM
Reply:

Similar Artilces:

MS Word French Calculations
I have some documents which do calculations. These were prepared in MS Word 2000. The construction is Tables and fill in fields. The formulas are written to produce results. When I try to use the same in Word 2007 French Edition it gives all kinds of error messages. 1) Does French edition require formulas to be written in some different syntax? 2) In general Word 2007 uses different syntax? Thanks Nihar Mody -- Nihar Mody ------------------------------------------------------------------------ Nihar Mody's Profile: http://forums.techarena.in/members/183443.h...

why in god's name won't calculated controls work?
I have been trying to no avail to create a calculated field on a form I have used the expression builder, I have typed in code manually, I have tried using full table and field name syntax all without success. In the control source I have =[transactions]![amount] * 2 The table and field names are correct ( they appear in the expression builder) and the amount field is a number data type. All this produces is #name? in the form field Every book and website I have consulted tells me that this should work. I am getting incredibly fed up with this pathe...

How do I auto calculate 90 days days from a certain date?
In a law office, we have to calculate from a date of filing either 30, 60 or 90 days from a certain date to calendar another due date item. Is there a way for Outlook to auto calculate 90 days from a date entered? If you're in the Calendar folder hit Control-G (for Go to) and enter the date followed by (without quotes) "+90d". You should end up on the day in question "T Cummings" <T Cummings@discussions.microsoft.com> wrote in message news:BF3927FC-7E90-433D-99DF-C9B32C5F384E@microsoft.com... > In a law office, we have to calculate from a date of filing...

Configuring the WEIGHTRONIX
We are using RMS 2.0 and are trying to configure the scale to be recognize by the RMS software. The scale seems to installed correctly on its own, but the interaction to read the weight inside the RMS, does not work. Here are the details : When we use the weight scale admin, it gives a corect weight. But after when we go the Microsoft RMS and selection the option to configure Microsoft opos service objects to configure the scale, it tells us weight - no data, status - error extended 114. Any thoughts on how to solve it with this particular scale ? We are using the Weightronix 6...

Change PivotChart View Axis Scale Min and Max
I have a Sub Form that shows my data in ChartView. As all the data is different I need to set the X-Axis scale to automatic. The problem with this is that Access does not do a great job, and sets my minimum value to 0. I would prefer to have my minimum a function of the smallest value being charted. ie [Min Record]*.9 Can I us VB or anthing else to set the X-Axis Min and/or Max? Please help ...

Pivot calculations, is it possible this way?
Hi, I've a datadump from a database containing work hours from five different departments, categorized as several different types like 'sick leave', 'on training' etc and of course the usual workstuff categories... I'ld want a pivot table of a specifik work type per department, like 'on training', but I'ld like the value to be proportional to the amount of personal on that specific department. Is that possible, how? Any help would be appreciated! Hi Sebastian, I can only assume that you already have the pivot table there... on the top left of the ...

VBA Make calculation on range
Hello, Is there a way i can make calculations on a range of cells without using using a for each.. next loop. I want to add 10 to the values in a range. You can do sheet1.range("A:A") = 10 but you can't do = sheet1.range("A:A") + 10. -- Talk to you later Nathan No loop is needed: Sub dural() Set r1 = Range("B1") Set r2 = Range("A1:A100") r1.Value = 10 r1.Copy r2.PasteSpecial Operation:=xlAdd End Sub -- Gary''s Student - gsnu2007L "Nathan" wrote: > Hello, > > Is there a way i can make calculations on a range of...

Running Calculations per day
Column A: days are listed for a year. (1-Jan to 31-Dec) Column B: contains numbers. Question: What would be the appropriate formula if you wanted Excel to SUM each days total from beginning of the year to today's date automatically, every day. Also, the remaining numbers to the end of the year automatically? Thus, if today was 9-July, Excel would automatically calculate all numbers in Column B from 1-Jan to 9-July. It would also calcualte the remaining numbers in Column B from 10-July to 31-Dec. Then next day, Excel would auto- calculate from 1-Jan to 10-July and from 11-July...

Calculate a forecasted increase using a Absolute Ref
Hello I am new to Excel and i am wanting to know how to Calculatet a Forecasted Increase using say P1 as an Absolute Reference and calculate the percentage increase on a monthly income. This is so the formula could then be replicated across without having to re-type the information in the Ref Cell ...

Footer page number scaling
Hi there, dont think there is a solution to this damn annoying problem I have, but you never know! When you create a footer to list the pages in a work book, they seem to be linked to the scaling of the print set up, so if I have a work book of 10 pages and 5 of them are say 100% and the other 5 are at 50% to fit to page the footer then becomes tiny on these pages and is not obviously consistent with the other pages when printed out and put together as a folder. Is there any way around this? I have tried using report manager add-in which from the looks of things is pretty much useless. I w...

Help on Simple calculation
I have a worksheet that tracks hours and minutes worked. The cells are formated as hh:mm. I can add up the hours and minutes worked by each person over a month and I can add up total time worked each day. What I also need is a grand total for each month. That is the total of each day worked. For some reason this just won't work, I have tried adding the daily total, and each persons totals but I just end up with a strange figure that bears no relation to anything as far as I can see. Please help, thank you. Hi, Format the grand total cell as [h]:mm. Regards, Jan Karel Pieterse ...

EXCEL- Is there a way to slow down the calculation speed?
I have a spreadsheet which has a large amount of DDE links int bloomberg. They have recently updated their API DDE so that the updat frequency is alot higher than before. This is causing the spreadshee to recalculate to often (pc running close to 100pct all the time). I there a registry value I can change that slows down the frequency o excel updating. I dont want to change to manual update due to the fac the spreadsheet has to be live and updating price changes. Any ideas -- Message posted from http://www.ExcelForum.com flashflash, You could se the calc mode to manual and then use a mac...

How to create a rolling list on a slide?
Trying to have a rolling list of names on a slide that will run uninterrupted until end of the list. Anyone know how? Is it possible? Thanks StevedinSF See if this helps: http://www.pptfaq.com/FAQ00207.htm -- Luc Sanders MVP - PowerPoint "StevedinSF" <StevedinSF@discussions.microsoft.com> schreef in bericht news:F7DB924D-7C34-42CF-B6DB-B04ADE9BAEB7@microsoft.com... > Trying to have a rolling list of names on a slide that will run > uninterrupted > until end of the list. > > Anyone know how? Is it possible? > > Thanks ...

scale/scanner question
does anyone know if the metrologic ms2020 scale/scanner will work with rms i know metrologic has good opos drivers i m not sure if rms has the ability to work with scale scanners? any help would be appriciated -- On Aug 6, 5:47 pm, "midwest pos" <morna...@msn.com> wrote: > does anyone know if the metrologic ms2020 scale/scanner will work with rms i > know metrologic has good opos drivers i m not sure if rms has the ability to > work with scale scanners? > any help would be appriciated > > -- Yes. I use them in my store and they do work. Jason www.gree...

Date Calculation #3
I have a spread sheet with dates entered in the range K2:K750 and in O2:O750. There are many dates that have not yet been entered. I am calculating the number of weeks between the two dates with the formula =(O2-K2)/7 and then filled the formula down to row 750. This is done in column P. My problem is that I must calculate the average number of weeks. How do I create a formula to calculate the average of values in the range P2:P750, without including those cells in rows where one or both of the dates in column K or O have not yet been entered. The formula should update as date informatio...

Calculated item in Pivottable
When any field is grouped in my pivottable, I can't add any calculated item and vice-versa. Each time I receive the error message: "Because a Pivottable report field is grouped, you cannot add a calculated item to this report..." I am stucked on this for months now; please help. ...

Calculation Log
Hi, I have programmed a valuation model in an Excel Worksheet with VBA. A full calculation round takes about six minutes. I am quite sure it can be cut down by 4 minutes at least by more efficient programming, but I wonder whether it is possible to get a dump of the caluclations that Excel does (And the time taken)? In that case I could easily identify the unnecessary calculations. Hope to hear from you... aki A tool to analyze your workbook can be downloaded here: www.decisionmodels.com Lots of tips about calculation speed too. -- Kind regards, Niek Otten Microsoft MVP - Excel &q...

Changing "Enable multi-threaded calculation" value
I need a way to globally change the Excel 2007 option for using all of the processors for calculations. I have run regmon and filemon and see nothing. Does anyone know of a GPO/ADM/Regkey that will allow me to control this? I want to change the value from "Use all the processors on this computer" to "Manual" I don't know of a registry setting for this. You can set it from VBA and its an application-level setting so would apply to all workbooks. You can control Excel 2007's multithreaded calculation from VBA using Application.MultiThreadedCalculation. App...

How do I calculate interest?
I want to create a spreadsheet that shows my kids the power of compounded interest. I want to show them what regular deposits against a specific monthly interest rate results in, at the end of the year. I want to have the interest rate in cell A1 and starting in row 5, in column A I have the date of deposit and in column B I have the deposited amount. Then I want to have a total that calculates the updated balance including the compounded interest rate each month. Any suggestions? TIA! Robert "darkwing_duck" <rbrown999@gmail.com> wrote: > I want to show them...

Huge differences in chart values screws my bar chart scale up.
Hi! I have a table which has mainly values between 0 and 5000 and I made bar chart from that data. Then I have 1 value which is over 100 000. This ofcourse makes all other bars way to small to be of any use. Is there any way to "truncate" this 1 bar so it wouldn't mess up the scale, but would show the right value anyway? Could I for example set different scale from 0 to 5000 and then from 5000 to 120 000? Regards, Aki Hi, Have a look at these examples of how to place a break in the Y axis. http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/...

Animation on multiple slides
Hi, I would like to create some kind of stop watch in my PP presentation in order to ensure that the presentation I do will take no longer than 10 minutes. I have created a rectangle and animated it in such a way that the rectangle will slowly grow in length from left to right growing to full length when the 10 minutes have passed. My problem is that I want the animated rectangle to continue on the next slide also. For instance if I turn to the next slide after 6 min I would like the rectangle to continue from 6 min and so on...... Can anyone help me with this? Thanks!...

how do I calculate a running average?
Week 1 Week 2 Week 3 Cym M. Share 5.46% 4.63% 0 Cym TRX 130 110 ADD TRX 2,381 2,377 TRX Needed 129 128 Difference 1 -18 Total Needed -17 Avg SOM I want to calculate Avg SOM cell. I eter new data each week and want a running average of Cym M. Share. I can't figure it out how to make it work. Assuming your first Cym M. share is in B2: =AVERAGE($B2:B2) then copy across the columns. "JBilly" wrote: > Week 1 Week 2 Week 3 > Cym M. Share 5.46% 4.63% 0 > Cym TRX ...

Calculating averages when some cells are blank?
Hi there, I am hoping some kind soul may be able to help me please? I am not sure what the correct term is for what I am trying to achieve so the Help file is useless to me at the moment...:-( I have a simple spreadsheet where the sum of six columns are summed at the end to give the average i.e. =SUM(C19:H19)/6 That causes no problem when all six columns contain data but...and there's always a but...what if some cells are empty and will be filled at a later date? If I leave the formula as (C19:H19)/6 and have any blank cells then it won't reflect the true average. I still want t...

Loan Calculation
Can I get some help with some loan calculation formula? I need to calculate payments based on 360/365/366/365.25 days in a year. That's one part. The other is, I also need to figure accurate payments if the days to first payment from date of contract is not the standard 30, say it is 1, 3, 10, 45, 60, 90, 100 etc, any number that the user can opt. How do I do this? Also I see some about last payment being different from the regular payments. WHat does that mean? Thanks Frank Hi, There are a load of financial functions built into excel. Have a look at the help pages for financial func...

Help with calculation #3
On sheet2 I have a list of dates where A1 is 01 Jan 2004, A2 is 01 Feb 2004 etc through to 01 Oct 2005, against each date is a column of figures. So A1 is the date and B1 is the volume. On sheet1 I want to have a formula that will calculate the total volume between 2 dates selected by the user, where D7 is the earliest date and D9 is the latest date. So after they have input the dates the volumes between those dates will be totaled and shown in E10.(They will only be able to input dates for the 1st of the month - so as to match the data held on sheet2.)As you appreciate there will be more tha...