馃攳
How to create Sliding Scale Commission Calculator in Excel - YouTube
Channel: Barb Henderson
[0]
today we're going to talk about a
sliding scale commission rate calculator
[6]
this is a problem that I work done for a
customer of mine and I'm going to
[9]
explain how I figured it out so here we
have a rate calculation on the amount
[19]
amount that they have earned Commission
on sold for $50,000 they would make
[23]
$1,250 commission or 2.5 percent now so
this works perfectly so if if you had
[36]
for example straight $50,000 you would
be an easy you would not have to do
[44]
any calculations you need to know
that you have to pay $1,250 in
[50]
commission unfortunately though people
make numbers that aren't exactly on the
[57]
on the chart here so what we're gonna
do this first let's figure out vlookup
[61]
for the values that we have here so I
entered in in C10 here i have entered
[67]
$210,000 so what we're gonna do is we'll
go to the first cell that I started
[73]
working on the calculation so we do they
Vlook up C10 and then
[79]
values are C12 E 23 and then we're we're
gonna look in the second column so in
[91]
the second column here and you get the
1200 you get the so for $200,000 you
[98]
forget 475 Commission and that's
exactly what the Vlook up has
[104]
brought us back now we have a value
entered in here more than $200,000 so
[112]
the first thing your gonna do is we're
going to calculate how much more than
[115]
$200,000 it is so we go into our next
formula and we say we take the value
[124]
that we have entered here which is two
hundred and ten thousand dollars minus
[129]
look up that value is C10 in value
[133]
in our chart here and we want
to look at the first column so we want
[139]
to find the one that's closest to C10 and minus it
so what happens is you end
[147]
up minusing $210,000 minus the
$200,000 from the vlookup in column 1
[154]
and you reach $10,000 so what we've done
in the next column here is the all we've
[162]
taken the $10,000 and we've looked on
the vlookup for the same values we've
[168]
looked at the $200,000 and we've gone
over to column three and we noticed
[173]
that yet 2.25% commison rate so you
multiply that $10,000 terms at 2.25% and
[183]
you end up with $225 now basically would
you have to do here is add your first
[191]
formula which is the Vlook up just
two straight plus the vlookup minus what
[199]
we've calculated for the extra
commission so now you come up with four
[204]
thousand nine hundred and seventy-five
dollars commission this works fine the
[209]
only thing that we have to do here to
finish off the formula is what happens
[214]
if you have a value that under $50,000
so now what we're gonna do is we're
[220]
gonna put an IF on there there
[222]
IF C10
[230]
is less than $50,000 there and we're
gonna do is we're gonna do C10 times
[248]
E12 which is our 2.25% and we will add up our
bracket and then we'll put
[264]
another one at the end here and we'll
get the same value but because we've put
[270]
the if in there so it's a we had a sale
sale of $45,000 and there you have
[282]
you have it is calculated it for less
than that now let's go 425 so now
[295]
it's worked perfectly for you have your
sliding rate Commission calculator I
[300]
know this is being confused and using it
is explained on my website Easy Excel
[305]
counters and the formulas are written out there for you as
well
Most Recent Videos:
You can go back to the homepage right here: Homepage