1. N

    Volume Tier Formula

    Hello, I am trying to create a formula in a block of cells that would parse volume into tiers based on cumulative volume but only tier the monthly volume as follows... Month Jan Feb Mar Apr May Jun Volume 1000 2500 3005 4000 5000...
  2. J

    Rate Table Calculation

    Hello, I am trying to compare the calculated prices for different rate tables in our billing system. Below are 2 examples: Table 1 <tbody> Tier From To Base Per 1 1 1 $100 $2 2 2 50 0 $1 3 51 9999 0 $.5 </tbody> Table 2 <tbody> Tier From To Base Per 1 1 5 $30 $5 2 6 10 $25 $4 3...
  3. T

    Need Lookup Value between a Range of Data

    I thought I knew how to do this, but I'm receiving a wrong value when the Max value is used as the lookup. Table: A B C Min Max Tier 0 300 0 301 800 1 801 1300 2 1301 1800 3 1801 2300 4 Table has a defined name of "Tiers"...
  4. S

    Will a SumProduct work with commission tiers

    Hi there Below is a commission tiers based on sales. So if someone makes $550k in sales, the commission will be $135k as per below. That calculation is very manual and I was wondering if a SumProduct function would work here. Excel WorkbookABCDEFG1Sales TiersCommission...
  5. S

    Calculating Commission based on tiers

    Hi there I’m trying to get a formula that calculates commission based on sales tiers. So in the below example A. B. C. % Commission 1. 0. 250,000. 0.3 2. 250,001. 500,000. 0.21 3. 500,001...
  6. S

    Tiered Pricing using SumProduct

    Hello, I am attempting to write a formula for distributed/cumulative tiered pricing and am not able to get the formula correctly. 1 - 30 units = $90/unit 31 - 60 units = $75/unit 61 - 100 units = $60/unit More than 100 units = $55/unit Examples of expected calculations are: 55 Units...
  7. L

    Formula for Calculating Cumulative Commission

    I need a formula for calculating the commission structure below. I am struggling with having the calculation recognize and calculate only if commission is accrued, since the first $5500 does not attract commission. Then once within the commission tiers, calculating only on the amounts within...
  8. D

    SumProduct with multiple tables

    I have found out, thanks to you wonderful people, how to use SumProduct formulas. Now I have a new question. What would be the best way to use the sumproduct formula for tiered pricing, if you have several different sets of tiers. For instance what if you have 5 customers and each of them has...
  9. E

    Calculating Quarterly Royalty Amounts on Cumulative Annual Tiered Royalty Rates

    A company gets paid, on product sales one quarter in arrears, the following royalties: Less than $100M in annual sales: 4.7% From $100M to $200M: 6.6% From $200M to $400M: 7.5% From $400M to $1,500M: 9.4% More than $1,500M: 9.3% Sales in a given year are: Q1: $43M Q2: $47M Q3: $55M Q4: $62M...
  10. P

    Tiered Commission Formual for a group of people

    Hi, I need to calculate the commission of my team, they get commission on credit card payments that they take. The tiers are: <TABLE class=MsoNormalTable style="MARGIN: auto auto auto -0.65pt; WIDTH: 217pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 0in 0in 0in"...

Some videos you may like

This Week's Hot Topics