Link to home
Start Free TrialLog in
Avatar of hkgal
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?
Avatar of SiddharthRout
SiddharthRout
Flag of India image

I am sorry but what are you exactly trying to do?

Sid
Avatar of hkgal
hkgal

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
Avatar of barry houdini
Try using the method shown here

regards, barry
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hkgal

ASKER

Thanks a lot!