Another "I can't quite get there" Sumproduct Forumla
I am trying to calculate the commision on sales using the percentages in
A1:C1 for each rep using the following criteria: For new sales for Products 1 or 2, Sum B10:B12 and G-I0:G12 then multiply by C7 For Existing sales of Product 1 or 2, sum A18:A20 and G18:G20 and multiply by A1 For increases for both I want to return the increased diference year over year in Rows 18-20 and then multiply only the difference by B5 Thank you for any guidance you can provide. I have been at this for so long that my head hurts for the effort. To help clarify: New sales for Product1 are in columns A, B C and Product2 in G, H I; Rows10-12 Existing sales are in Rows 18-20 Here is what the data looks like: A B C D G H I 1 3% 5% 7% 9 Rep 2005 2006 2007 2005 2006 2007 10 Joe $2,766.00 $2,884.00 $3,489.88 $600.00 $1,734.00 $533.65 11 Fred $1,857.00 $700.00 $0.0 $3,552.00 $2,886.00 $5,051.29 12 Sally $2,334.00 $3,894.00 $4,125.18 $11,065.99 $7,860.00 $6,254.12 18 Sally $2,766.00 $2,884.00 $3,489.88 $600.00 $1,734.00 $533.65 19 Fred $1,857.00 $700.00 $0.0 $3,552.00 $2,886.00 $5,051.29 20 Fred $2,334.00 $3,894.00 $4,125.18 $11,065.99 $7,860.00 $6,254.12 |
Another "I can't quite get there" Sumproduct Forumla
I forgot to mention that if the difference in existing sales results in a
negative number then it should be returned as a zero and not a negative number that would reduce the other product calculation. "ladara tech" wrote: I am trying to calculate the commision on sales using the percentages in A1:C1 for each rep using the following criteria: For new sales for Products 1 or 2, Sum B10:B12 and G-I0:G12 then multiply by C7 For Existing sales of Product 1 or 2, sum A18:A20 and G18:G20 and multiply by A1 For increases for both I want to return the increased diference year over year in Rows 18-20 and then multiply only the difference by B5 Thank you for any guidance you can provide. I have been at this for so long that my head hurts for the effort. To help clarify: New sales for Product1 are in columns A, B C and Product2 in G, H I; Rows10-12 Existing sales are in Rows 18-20 Here is what the data looks like: A B C D G H I 1 3% 5% 7% 9 Rep 2005 2006 2007 2005 2006 2007 10 Joe $2,766.00 $2,884.00 $3,489.88 $600.00 $1,734.00 $533.65 11 Fred $1,857.00 $700.00 $0.0 $3,552.00 $2,886.00 $5,051.29 12 Sally $2,334.00 $3,894.00 $4,125.18 $11,065.99 $7,860.00 $6,254.12 18 Sally $2,766.00 $2,884.00 $3,489.88 $600.00 $1,734.00 $533.65 19 Fred $1,857.00 $700.00 $0.0 $3,552.00 $2,886.00 $5,051.29 20 Fred $2,334.00 $3,894.00 $4,125.18 $11,065.99 $7,860.00 $6,254.12 |
All times are GMT +1. The time now is 10:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com