# Calculating a blended rate

1. ## Calculating a blended rate

I'm looking to do something similar to sliding scale commission but with fewer variables. Attached example worksheet, which goes like this:

Cells D2:H2 contain threshold numbers ("buckets") (2,000, 4,000, 6,000, 8,000)
Cells D3:H3 contain dollar amount per unit in each bucket (\$200, \$180, \$140, \$120)

I want to calculate the total cost for a number that falls in between two of the buckets, for example 2,500

The first 2,000 are multiplied by \$200 and the next 500 are multiplied by \$180.

How can I write the function most simply?

2. ## Re: Calculating a blended rate

Hi,

1. Change of data table.

2. Try this formula in D10:

=SUMPRODUCT((D9>=\$D\$2:\$I\$2)*(D9-\$D\$2:\$I\$2)*(\$E\$3:\$J\$3-\$D\$3:\$I\$3))

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1