Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alternatives to embedded ifs
I'm trying to model a pricing structure based on volume tiers, but
there are too many embedded if statements and the function won't work. It's a 'step-pricing model' meaning that the first 12000 transactions are 0.39 each, then the next 18,000 transactions up to 30,000 are 0.37 each, then the next 30,000 up to 60,000 are 0.35 and so on (see below). How can I determine revenue from this pricing scenario? I had originally used embedded 'if' statements but my Excel 2003 won't allow me more than 7. Is there any other way to do this? 12,000 0.39 30,000 0.37 60,000 0.35 120,000 0.33 180000 0.31 240000 0.29 300000 0.27 360000 0.25 480000 0.23 600000 0.21 720000 0.19 900000 0.17 1200000 0.15 1800000 0.13 2400000 0.11 3000000 0.09 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alternatives to embedded ifs
See this:
http://mcgimpsey.com/excel/variablerate.html -- Biff Microsoft Excel MVP "jaime_ottawa" wrote in message ... I'm trying to model a pricing structure based on volume tiers, but there are too many embedded if statements and the function won't work. It's a 'step-pricing model' meaning that the first 12000 transactions are 0.39 each, then the next 18,000 transactions up to 30,000 are 0.37 each, then the next 30,000 up to 60,000 are 0.35 and so on (see below). How can I determine revenue from this pricing scenario? I had originally used embedded 'if' statements but my Excel 2003 won't allow me more than 7. Is there any other way to do this? 12,000 0.39 30,000 0.37 60,000 0.35 120,000 0.33 180000 0.31 240000 0.29 300000 0.27 360000 0.25 480000 0.23 600000 0.21 720000 0.19 900000 0.17 1200000 0.15 1800000 0.13 2400000 0.11 3000000 0.09 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alternatives to embedded ifs
jaime_ottawa wrote:
I'm trying to model a pricing structure based on volume tiers, but there are too many embedded if statements and the function won't work. It's a 'step-pricing model' meaning that the first 12000 transactions are 0.39 each, then the next 18,000 transactions up to 30,000 are 0.37 each, then the next 30,000 up to 60,000 are 0.35 and so on (see below). How can I determine revenue from this pricing scenario? I had originally used embedded 'if' statements but my Excel 2003 won't allow me more than 7. Is there any other way to do this? 12,000 0.39 30,000 0.37 60,000 0.35 120,000 0.33 180000 0.31 240000 0.29 300000 0.27 360000 0.25 480000 0.23 600000 0.21 720000 0.19 900000 0.17 1200000 0.15 1800000 0.13 2400000 0.11 3000000 0.09 Assuming I understand your needs properly and your table above is in A2:B17 and A1 is blank, put the number of transactions in D2 and this in E2: =SUMPRODUCT((A2:A17<=D2)*(A2:A17-A1:A16)*(B2:B17)) +(D2-INDEX(A2:A17,MATCH(D2,A2:A17,1)))* INDEX(B2:B17,MATCH(D2,A2:A17,1)+1) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alternatives to embedded ifs
Glenn wrote:
jaime_ottawa wrote: I'm trying to model a pricing structure based on volume tiers, but there are too many embedded if statements and the function won't work. It's a 'step-pricing model' meaning that the first 12000 transactions are 0.39 each, then the next 18,000 transactions up to 30,000 are 0.37 each, then the next 30,000 up to 60,000 are 0.35 and so on (see below). How can I determine revenue from this pricing scenario? I had originally used embedded 'if' statements but my Excel 2003 won't allow me more than 7. Is there any other way to do this? 12,000 0.39 30,000 0.37 60,000 0.35 120,000 0.33 180000 0.31 240000 0.29 300000 0.27 360000 0.25 480000 0.23 600000 0.21 720000 0.19 900000 0.17 1200000 0.15 1800000 0.13 2400000 0.11 3000000 0.09 Assuming I understand your needs properly and your table above is in A2:B17 and A1 is blank, put the number of transactions in D2 and this in E2: =SUMPRODUCT((A2:A17<=D2)*(A2:A17-A1:A16)*(B2:B17)) +(D2-INDEX(A2:A17,MATCH(D2,A2:A17,1)))* INDEX(B2:B17,MATCH(D2,A2:A17,1)+1) Correction...A1 should be zero and E2 is as follows: =SUMPRODUCT((A2:A17<=D2)*(A2:A17-A1:A16)*(B2:B17))+ (D2-INDEX(A1:A17,MATCH(D2,A1:A17,1)))* INDEX(B2:B17,MATCH(D2,A1:A17,1)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alternatives to embedded ifs
On Jul 22, 12:14*pm, Glenn wrote:
jaime_ottawa wrote: I'm trying to model a pricing structure based on volume tiers, but there are too many embedded if statements and the function won't work. It's a 'step-pricing model' meaning that the first 12000 transactions are 0.39 each, then the next 18,000 transactions up to 30,000 are 0.37 each, then the next 30,000 up to 60,000 are 0.35 and so on (see below). How can I determine revenue from this pricing scenario? I had originally used embedded 'if' statements but my Excel 2003 won't allow me more than 7. Is there any other way to do this? *12,000 * *0.39 30,000 * * 0.37 60,000 * * 0.35 120,000 * *0.33 180000 * * 0.31 240000 * * 0.29 300000 * * 0.27 360000 * * 0.25 480000 * * 0.23 600000 * * 0.21 720000 * * 0.19 900000 * * 0.17 1200000 * *0.15 1800000 * *0.13 2400000 * *0.11 3000000 * *0.09 Assuming I understand your needs properly and your table above is in A2:B17 and A1 is blank, put the number of transactions in D2 and this in E2: =SUMPRODUCT((A2:A17<=D2)*(A2:A17-A1:A16)*(B2:B17)) +(D2-INDEX(A2:A17,MATCH(D2,A2:A17,1)))* INDEX(B2:B17,MATCH(D2,A2:A17,1)+1) - Hide quoted text - - Show quoted text - This didnt' work.. I entered this: My volumes are v14:v29 and my pricing is x14:x29 with v13 blank. J12 is my volume. Am I doing something wrong? Do the two columns need to be beside each other or something? =SUMPRODUCT(($V$14:$V$29<=J12)*($V$14:$V$29-$V$13:$V$28)*($X$14:$X$29)) +(J12-INDEX($V$14:$V$29,MATCH(J12,$V$14:$V$29,1)))*INDEX ($X$14:$X $29,MATCH(J12,$V$14:$V$29,1)+1) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alternatives to embedded ifs
jaime_ottawa wrote:
On Jul 22, 12:14 pm, Glenn wrote: jaime_ottawa wrote: I'm trying to model a pricing structure based on volume tiers, but there are too many embedded if statements and the function won't work. It's a 'step-pricing model' meaning that the first 12000 transactions are 0.39 each, then the next 18,000 transactions up to 30,000 are 0.37 each, then the next 30,000 up to 60,000 are 0.35 and so on (see below). How can I determine revenue from this pricing scenario? I had originally used embedded 'if' statements but my Excel 2003 won't allow me more than 7. Is there any other way to do this? 12,000 0.39 30,000 0.37 60,000 0.35 120,000 0.33 180000 0.31 240000 0.29 300000 0.27 360000 0.25 480000 0.23 600000 0.21 720000 0.19 900000 0.17 1200000 0.15 1800000 0.13 2400000 0.11 3000000 0.09 Assuming I understand your needs properly and your table above is in A2:B17 and A1 is blank, put the number of transactions in D2 and this in E2: =SUMPRODUCT((A2:A17<=D2)*(A2:A17-A1:A16)*(B2:B17)) +(D2-INDEX(A2:A17,MATCH(D2,A2:A17,1)))* INDEX(B2:B17,MATCH(D2,A2:A17,1)+1) - Hide quoted text - - Show quoted text - This didnt' work.. I entered this: My volumes are v14:v29 and my pricing is x14:x29 with v13 blank. J12 is my volume. Am I doing something wrong? Do the two columns need to be beside each other or something? =SUMPRODUCT(($V$14:$V$29<=J12)*($V$14:$V$29-$V$13:$V$28)*($X$14:$X$29)) +(J12-INDEX($V$14:$V$29,MATCH(J12,$V$14:$V$29,1)))*INDEX ($X$14:$X $29,MATCH(J12,$V$14:$V$29,1)+1) =SUMPRODUCT(($X$14:$X$29<=J12)*($X$14:$X$29-$X$13:$X$28)*($V$14:$V$29))+ (J12-INDEX($X$13:$X$29,MATCH(J12,$X$13:$X$29,1)))* INDEX($V$14:$V$30,MATCH(J12,$X$13:$X$29,1)) Includes the correction from my previous post. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alternatives to embedded ifs
That would be do-able if the steps were at regular intervals.
You could use vlookup on a hidden table with range_lookup (the optional parameter at the end) set to True and do it that way? "jaime_ottawa" wrote: I'm trying to model a pricing structure based on volume tiers, but there are too many embedded if statements and the function won't work. It's a 'step-pricing model' meaning that the first 12000 transactions are 0.39 each, then the next 18,000 transactions up to 30,000 are 0.37 each, then the next 30,000 up to 60,000 are 0.35 and so on (see below). How can I determine revenue from this pricing scenario? I had originally used embedded 'if' statements but my Excel 2003 won't allow me more than 7. Is there any other way to do this? 12,000 0.39 30,000 0.37 60,000 0.35 120,000 0.33 180000 0.31 240000 0.29 300000 0.27 360000 0.25 480000 0.23 600000 0.21 720000 0.19 900000 0.17 1200000 0.15 1800000 0.13 2400000 0.11 3000000 0.09 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect alternatives | Excel Worksheet Functions | |||
Indirect alternatives | Excel Discussion (Misc queries) | |||
Alternatives to GET.CELL and VB? | Excel Worksheet Functions | |||
What are the alternatives ??? | Excel Discussion (Misc queries) | |||
Alternatives to Excel | Excel Discussion (Misc queries) |