hkgal

asked on

# Excel set formula for ranges

I've a Excel problem don't know how to set formula:

ColA ColB

0 0%

1 - 50000 10%

50001 - 80000 20%

80001 - 100000 30%

>100000 40%

Then I've a series of numbers:

ColC

3994

113140

4141411111

67

81000

Now I need to calculate like: e.g. 81000 --> 50000*10%+30000*20%+1000*30%

how can I do this?

ColA ColB

0 0%

1 - 50000 10%

50001 - 80000 20%

80001 - 100000 30%

>100000 40%

Then I've a series of numbers:

ColC

3994

113140

4141411111

67

81000

Now I need to calculate like: e.g. 81000 --> 50000*10%+30000*20%+1000*3

how can I do this?

ASKER

In ColC, all those figures need to be breakdown and calculate by ranges,

e.g. 81000 --> 50000*10%+30000*20%+1000*30%

this is, 81000, the 1st 50000 are on 10% commission, then 30000 on 20% commission, finally the 1000 left (which falls within 80001 - 100000 will be on 30% commission)

so the final result will be 5000+6000+300=11300

e.g. 81000 --> 50000*10%+30000*20%+1000*3

this is, 81000, the 1st 50000 are on 10% commission, then 30000 on 20% commission, finally the 1000 left (which falls within 80001 - 100000 will be on 30% commission)

so the final result will be 5000+6000+300=11300

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thanks a lot!

Sid