Thanks a bazillion JE :-)
What you posted is sooooooooo much shorter and cleaner than what I had
been able to come up with so far.
I believe I did exactly as you said and ran into two small problems
unfortunately. Perhaps I put something in the wrong place ?
Monthly sales start in B2 with ytd in c2. C3 reads =c2+b3.
One problem is with the cells for future months with nothing entered.
Here is what I get.
Monthly YTD running total Amt Owed
50000 50000 15000
49000 99000 14700
11000 110000 18600
0 110000 14700
0 110000 18600
0 110000 14700
0 110000 18600
0 110000 14700
For the future months, the YTD carries down via my forumla and an AMT
OWED is calculated. I tried to hide the YTD by doing IF(b5=0,0,b5+c4)
and while it put zeroes in the YTD, the amt owed was still calculated.
The second (and most important) problem is located on line 4 were the
YTD splits tiers.
YTD was at 99,000 and 11,000 was added. End result should be ($1000*.3)
and ($10,000*.33) which would give you $300+$3300, or $3600 in amt owed.
But it is coming up with $18600.
Unfortunately, I can't go in and tinker around with what you provided as
I have absolutely no idea whatsoever about SUMPRODUCT functions.
I have double checked to make sure that I followed your directions, but
must admit that I could very easily be missing something.
Could you pop the same numbers in on your side and see if you get the
same results ?
Thanks,
Dave
JE McGimpsey wrote in news:jemcgimpsey-
:
I would guess that something like this will suit your needs:
J K L
1 Threshold Marginal Rate Diff. Rate
2 0 30% =K2
3 100000 33% =K3-K2
4 300000 35% =K4-K3
Now, assume your monthly sales start in B2, with year-to-date sales in
C2. Then the first month's commission is
D2: =SUMPRODUCT(--(C2$J$2:$J$4),(C2-$J$2:$J$4),$L$2:$L$4)
subsequent months' commissions will then just be the total calculated
commission, less the commission paid to date:
D3: =SUMPRODUCT(--(C3$J$2:$J$4),(C3-$J$2:$J$4),$L$2:$L$4) - D2
Copy D3 down as far as necessary.
In article ,
asdfasdf wrote:
Thanks for the link Aladin,
I checked the site out and it is on the right track, but there are
differences that I can't work out.
Here is what I tried to work with from McGimpsey's.