Home |
Search |
Today's Posts |
#1
|
|||
|
|||
My formula
I am trying to create a formula for my consignment business and I am having a
little trouble. Here is what I need the formula to do: If item sells for 300 or less 25% times the sales price, If item sells for300.01- 500, 25% of first 300 + 20% of the remaining amount betweem 300.01-500. If it sells for 500.01-1000, 25% of first 300, +20% of 300.01-500,+ 15%the remaining amount between 500.01-1000. If item sells for 1000 or more, 25% of first 300, +20% of 300.01-500, +15% of 500.01-1000, + 10% of the remaining amount over $1,000. ex. I sell a $750 guitar at my store I recieve 25% of first $300= $75 20% of 300.01-500= $40 15% of 500.01-750=$37.5 Total Commission= $152.5 I enter the following formula: IF(E22<300,SUM(E22)*25%,SUM((E22-300)*20%+75)) This formula allows me to solve the total commission for items under $300, and Items over $300, however, I can only assign 20% commission to items over $300. How can I create multiple rules for a single cell? |
#2
|
|||
|
|||
Here's one try ..
In Sheet1 (Set up a reference table) ----------- List in A2:A5 : 0, 300.01, 500.01, 1000.01 List in B2:B5 : 25%, 20%, 15%, 10% Put in: C3: =B2*(A3-0.01) C4: =B3*(A4-A3)+C3 C5: =B4*(A5-A4)+C4 In Sheet2 ---------- Assume your sales are listed in E22 down Put in F22: =VLOOKUP(E22,Sheet1!$A$2:$C$5,3,TRUE)+(E22-TRUNC(VLOOKUP(E22,Sheet1!$A$2:$C$5,1,TRUE)))*VLOOK UP(E22,Sheet1!$A$2:$C$5,2,TRUE) Copy F22 down This should return your total commissions in col F Some sample test values / returns in E22:F28 a 750 152.5 300 75 350 85 500 115 600 130 1000 190 1500 240 (seems ok ?) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Mr.Consignment" wrote: I am trying to create a formula for my consignment business and I am having a little trouble. Here is what I need the formula to do: If item sells for 300 or less 25% times the sales price, If item sells for300.01- 500, 25% of first 300 + 20% of the remaining amount betweem 300.01-500. If it sells for 500.01-1000, 25% of first 300, +20% of 300.01-500,+ 15%the remaining amount between 500.01-1000. If item sells for 1000 or more, 25% of first 300, +20% of 300.01-500, +15% of 500.01-1000, + 10% of the remaining amount over $1,000. ex. I sell a $750 guitar at my store I recieve 25% of first $300= $75 20% of 300.01-500= $40 15% of 500.01-750=$37.5 Total Commission= $152.5 I enter the following formula: IF(E22<300,SUM(E22)*25%,SUM((E22-300)*20%+75)) This formula allows me to solve the total commission for items under $300, and Items over $300, however, I can only assign 20% commission to items over $300. How can I create multiple rules for a single cell? |
#3
|
|||
|
|||
Mr Consignment,
Try: =IF(E22<=300,E22*25%,75)+IF(AND(E22300,E22<=500), (E22-300)*20%,(E22300)*40 )+IF(AND(E22500,E22<=1000),(E22-500)*15%,(E22500)*75)+(E221000)*(E22-1000 )*10% or without any IF's but with one more function call try: =MIN(E22,300)*25%+MAX(MIN(E22-300,200)*20%,0)+MAX(MIN(E22-500,500)*15%,0)+MA X(E22-1000,0)*10% HTH Sandy -- to e-mail direct replace @mailintor.com with @tiscali.co.uk "Mr.Consignment" wrote in message ... I am trying to create a formula for my consignment business and I am having a little trouble. Here is what I need the formula to do: If item sells for 300 or less 25% times the sales price, If item sells for300.01- 500, 25% of first 300 + 20% of the remaining amount betweem 300.01-500. If it sells for 500.01-1000, 25% of first 300, +20% of 300.01-500,+ 15%the remaining amount between 500.01-1000. If item sells for 1000 or more, 25% of first 300, +20% of 300.01-500, +15% of 500.01-1000, + 10% of the remaining amount over $1,000. ex. I sell a $750 guitar at my store I recieve 25% of first $300= $75 20% of 300.01-500= $40 15% of 500.01-750=$37.5 Total Commission= $152.5 I enter the following formula: IF(E22<300,SUM(E22)*25%,SUM((E22-300)*20%+75)) This formula allows me to solve the total commission for items under $300, and Items over $300, however, I can only assign 20% commission to items over $300. How can I create multiple rules for a single cell? |
#4
|
|||
|
|||
Max wrote...
In Sheet1 (Set up a reference table) ----------- List in A2:A5 : 0, 300.01, 500.01, 1000.01 List in B2:B5 : 25%, 20%, 15%, 10% Put in: C3: =B2*(A3-0.01) C4: =B3*(A4-A3)+C3 C5: =B4*(A5-A4)+C4 In Sheet2 ---------- Assume your sales are listed in E22 down Put in F22: =VLOOKUP(E22,Sheet1!$A$2:$C$5,3,TRUE)+(E22-TRUNC(VLOOKUP(E22,Sheet1!$A$2:$C$5,1,TRUE))) *VLOOKUP(E22,Sheet1!$A$2:$C$5,2,TRUE) .... No need to include the 4th argument to VLOOKUP when it's TRUE. What's gained? This could be done with a single formula without ancillary cells. =SUMPRODUCT(IF(C6{300;500;1000;""},{300;500;1000; ""},C6)-{0;300;500;1000}, IF(C6{0;300;500;1000},{0.25;0.2;0.15;0.1})) |
#5
|
|||
|
|||
See
http://www.mcgimpsey.com/excel/variablerate.html In article , "Mr.Consignment" wrote: I am trying to create a formula for my consignment business and I am having a little trouble. Here is what I need the formula to do: If item sells for 300 or less 25% times the sales price, If item sells for300.01- 500, 25% of first 300 + 20% of the remaining amount betweem 300.01-500. If it sells for 500.01-1000, 25% of first 300, +20% of 300.01-500,+ 15%the remaining amount between 500.01-1000. If item sells for 1000 or more, 25% of first 300, +20% of 300.01-500, +15% of 500.01-1000, + 10% of the remaining amount over $1,000. ex. I sell a $750 guitar at my store I recieve 25% of first $300= $75 20% of 300.01-500= $40 15% of 500.01-750=$37.5 Total Commission= $152.5 I enter the following formula: IF(E22<300,SUM(E22)*25%,SUM((E22-300)*20%+75)) This formula allows me to solve the total commission for items under $300, and Items over $300, however, I can only assign 20% commission to items over $300. How can I create multiple rules for a single cell? |
#6
|
|||
|
|||
In article .com,
"Harlan Grove" wrote: No need to include the 4th argument to VLOOKUP when it's TRUE. What's gained? Clarity & specificity? Given that the majority of users don't even know that there *is* a fourth argument, I usually include it explicitly to make it clear that I intended the lookup to return an approximate match. What's gained by leaving it out, other than a few keystrokes? This could be done with a single formula without ancillary cells. That's a far better solution, but it could be significantly simplified... =SUMPRODUCT(--(C6{0,300,500,1000}),(C6-{0,300,500,1000}),{0.25,-0.05, -0.05,-0.05}) See http://www.mcgimpsey.com/excel/variablerate.html |
#7
|
|||
|
|||
Thanks for the experienced touches, guys !
Something to aspire to .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#8
|
|||
|
|||
JE McGimpsey wrote...
"Harlan Grove" wrote: No need to include the 4th argument to VLOOKUP when it's TRUE. What's gained? Clarity & specificity? .... If clarity of code were important, we wouldn't be using spreadsheets. As for specificity, if the default value is well-defined (which it is), there's nothing gained by including the default value of the 4th arg. Of course, you may be using a different definition of 'specificity'. What's gained by leaving it out, other than a few keystrokes? Compatibility with other spreadsheets, FWLIW these days. Also, omitting default arguments reduces file size. And, in extreme cases, it's necessary to do so in very long formulas in order to remain under Excel's formula length limit. Besides, the more keystrokes there are, the more opportunities there are for tpyos. |
#9
|
|||
|
|||
In article . com,
"Harlan Grove" wrote: If clarity of code were important, we wouldn't be using spreadsheets. OK, you've got me there... As for specificity, if the default value is well-defined (which it is), there's nothing gained by including the default value of the 4th arg. Of course, you may be using a different definition of 'specificity'. Specificity in this case only meaning completely specifying the arguments. Yes the fourth argument's default case is well defined, but for me it's a habit that prevents errors. It indicates that the choice of approximate or exact match has been explicitly made. Makes troubleshooting easier, too - I don't have to question whether the argument was inadvertently left out, or if an approximate match was intended. What's gained by leaving it out, other than a few keystrokes? Compatibility with other spreadsheets, FWLIW these days. Also, omitting default arguments reduces file size. And, in extreme cases, it's necessary to do so in very long formulas in order to remain under Excel's formula length limit. Valid points. In my case, however, I don't have any clients that know enough about other spreadsheets that would make this a problem, nor do I typically do enough VLOOKUPS to make the extra bytes an issue. I don't remember if the TRUE/FALSE is tokenized or not... I also try hard not to have very long formulae - they're hell to maintain, especially 6 months or more after delivery. Besides, the more keystrokes there are, the more opportunities there are for tpyos. <g |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Cell shows formula and not the result of the formula. | Excel Worksheet Functions | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |