Share via

Formula Query

Anonymous
2024-10-30T12:33:18+00:00

Hi,

I was wondering if anyone could assist please.

At work, we are changing the way we charge commission on our sales, so if sales are equal to or below £100k, then we will charge 16.75%, if it is between £100,000.01 and £200k, then it will be 14.75%, and if it is over £200k, then it will be 12.75%.

In one column, I have the total sales for the year for one customer, so my question is, is there a formula that can work out the exact amount owed so that if for example, sales are £210k, then the formula charges 16.75% on the first £100k, 14.75% on the next £100k and 12.75% on the remaining £10K?

Any assistance is appreciated.

Many thanks in advance!

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points
    2024-10-30T12:54:19+00:00

    I'd use a lookup table listing the thresholds, the rates and the difference between the tiers. In the screenshot below, that table is in D2:F4 but it can be anywhere: on the same sheet, or on another sheet, even a hidden one.

    The formulas look like this:

    The formula in B2 is

    =SUM((A2>$D$2:$D$4)*(A2-$D$2:$D$4)*$F$2:$F$4)

    This can be filled down.

    Was this answer helpful?

    0 comments