Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combination...SumProduct, Index, Match?
Hello!
Life was simple, then we offered multiple rates for the same paper. Your assistance with the following scenario is greatly appreciated. -Monte Rate Chart Tier....Paper1...Paper2...Paper3 ...1.......$1.00.....$5.00.....$7.00 ...2.......$2.00.....$5.00.....$7.00 ...3.......$3.00.....$5.00.....$7.00 ...4.......$4.00.....$5.00.....$7.00 Customer Accounts Customer.....Tier.....Paper1.....Paper2.....Paper3 .....Rate.....Due Customer1......1..........1....................... ................$1.00....$1.00 Customer2......2..........4....................... ................$2.00....$8.00 Customer3......3..........X....................... ................$3.00....$0.00 Customer4......1.........................1........ ................$5.00....$5.00 Customer5......4.................................. ......X.........$7.00....$0.00 When the customer subscribes to paper1, use the customers tier number to find the corresponding rate on the rate chart. Do this even if the customer is not currently receiving the paper (marked with an x). If the customer subscribes to paper2 or paper3, regardless of the customers tier number, the rate is always the same. Last, for each customer, the amount due is always the quantity of each paper multiplied by the rate, except when the customer is not receiving the paper (marked with an x), then its zero. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combination...SumProduct, Index, Match?
Assumptions:
Tier Table is defined as TblTier The first row of the Tier Table is defined as Tier The Customer's Tier column is defined as CustTier I have your table for customers beginning in row 7, with customer 1 in cell A8, etc. The formula in F8: (rate) =VLOOKUP(CustTier,TblTier,MATCH(IF(C8<"","Paper1" ,"")&IF(D8<"","Paper2","")&IF(E8<"","Paper3","") ,Tier,0),FALSE) The formula in G8: =SUM(C8:E8)*F8 This also assumes that each customer will only have 1 possibility in either Paper1, Paper2, or Paper3. Multiple entries break it. Hope this helps. -- John C "Monte" wrote: Hello! Life was simple, then we offered multiple rates for the same paper. Your assistance with the following scenario is greatly appreciated. -Monte Rate Chart Tier....Paper1...Paper2...Paper3 ..1.......$1.00.....$5.00.....$7.00 ..2.......$2.00.....$5.00.....$7.00 ..3.......$3.00.....$5.00.....$7.00 ..4.......$4.00.....$5.00.....$7.00 Customer Accounts Customer.....Tier.....Paper1.....Paper2.....Paper3 .....Rate.....Due Customer1......1..........1....................... ................$1.00....$1.00 Customer2......2..........4....................... ................$2.00....$8.00 Customer3......3..........X....................... ................$3.00....$0.00 Customer4......1.........................1........ ................$5.00....$5.00 Customer5......4.................................. ......X.........$7.00....$0.00 When the customer subscribes to paper1, use the customers tier number to find the corresponding rate on the rate chart. Do this even if the customer is not currently receiving the paper (marked with an x). If the customer subscribes to paper2 or paper3, regardless of the customers tier number, the rate is always the same. Last, for each customer, the amount due is always the quantity of each paper multiplied by the rate, except when the customer is not receiving the paper (marked with an x), then its zero. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combination...SumProduct, Index, Match?
John-
I really appreciate the assistance. It looks good so far. One question as I adjust it to fit the business... Is there a limit to the number of &IF cirteria? I have 15 papers. Thanks again. -Monte "John C" wrote: Assumptions: Tier Table is defined as TblTier The first row of the Tier Table is defined as Tier The Customer's Tier column is defined as CustTier I have your table for customers beginning in row 7, with customer 1 in cell A8, etc. The formula in F8: (rate) =VLOOKUP(CustTier,TblTier,MATCH(IF(C8<"","Paper1" ,"")&IF(D8<"","Paper2","")IF(E8<"","Paper3",""), Tier,0),FALSE) The formula in G8: =SUM(C8:E8)*F8 This also assumes that each customer will only have 1 possibility in either Paper1, Paper2, or Paper3. Multiple entries break it. Hope this helps. -- John C "Monte" wrote: Hello! Life was simple, then we offered multiple rates for the same paper. Your assistance with the following scenario is greatly appreciated. -Monte Rate Chart Tier....Paper1...Paper2...Paper3 ...1.......$1.00.....$5.00.....$7.00 ...2.......$2.00.....$5.00.....$7.00 ...3.......$3.00.....$5.00.....$7.00 ...4.......$4.00.....$5.00.....$7.00 Customer Accounts Customer.....Tier.....Paper1.....Paper2.....Paper3 .....Rate.....Due Customer1......1..........1....................... ................$1.00....$1.00 Customer2......2..........4....................... ................$2.00....$8.00 Customer3......3..........X....................... ................$3.00....$0.00 Customer4......1.........................1........ ................$5.00....$5.00 Customer5......4.................................. ......X.........$7.00....$0.00 When the customer subscribes to paper1, use the customers tier number to find the corresponding rate on the rate chart. Do this even if the customer is not currently receiving the paper (marked with an x). |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combination...SumProduct, Index, Match?
John-
I tested and answered my own question. I have not found a limit to the number of "&IF" criteria within the MATCH formula. Thanks again. -Monte ========== "Monte" wrote: John- I really appreciate the assistance. It looks good so far. One question as I adjust it to fit the business... Is there a limit to the number of &IF cirteria? I have 15 papers. Thanks again. -Monte ========== "Monte" wrote: John- I really appreciate the assistance. It looks good so far. One question as I adjust it to fit the business... Is there a limit to the number of &IF cirteria? I have 15 papers. Thanks again. -Monte ========== "John C" wrote: Assumptions: Tier Table is defined as TblTier The first row of the Tier Table is defined as Tier The Customer's Tier column is defined as CustTier I have your table for customers beginning in row 7, with customer 1 in cell A8, etc. The formula in F8: (rate) =VLOOKUP(CustTier,TblTier,MATCH(IF(C8<"","Paper1" ,"")&IF(D8<"","Paper2","")IF(E8<"","Paper3",""), Tier,0),FALSE) The formula in G8: =SUM(C8:E8)*F8 This also assumes that each customer will only have 1 possibility in either Paper1, Paper2, or Paper3. Multiple entries break it. Hope this helps. -- John C ========== "Monte" wrote: Hello! Life was simple, then we offered multiple rates for the same paper. Your assistance with the following scenario is greatly appreciated. -Monte Rate Chart Tier....Paper1...Paper2...Paper3 ...1.......$1.00.....$5.00.....$7.00 ...2.......$2.00.....$5.00.....$7.00 ...3.......$3.00.....$5.00.....$7.00 ...4.......$4.00.....$5.00.....$7.00 Customer Accounts Customer.....Tier.....Paper1.....Paper2.....Paper3 .....Rate.....Due Customer1......1..........1....................... ................$1.00....$1.00 Customer2......2..........4....................... ................$2.00....$8.00 Customer3......3..........X....................... ................$3.00....$0.00 Customer4......1.........................1........ ................$5.00....$5.00 Customer5......4.................................. ......X.........$7.00....$0.00 When the customer subscribes to paper1, use the customers tier number to find the corresponding rate on the rate chart. Do this even if the customer is not currently receiving the paper (marked with an x). |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combination...SumProduct, Index, Match?
You might consider a change to your setup....Instead of having a column for
each Paper1 through Paper15, have a column for each customer that has their Paper#. This would also simplify the formula. You could then name the column with the Paper# something like CustPaper, and your formula would now be much simpler for rate: =VLOOKUP(CustTier,TblTier,MATCH(CustPaper,Tier,0), FALSE) -- John C "Monte" wrote: John- I tested and answered my own question. I have not found a limit to the number of "&IF" criteria within the MATCH formula. Thanks again. -Monte ========== "Monte" wrote: John- I really appreciate the assistance. It looks good so far. One question as I adjust it to fit the business... Is there a limit to the number of &IF cirteria? I have 15 papers. Thanks again. -Monte ========== "Monte" wrote: John- I really appreciate the assistance. It looks good so far. One question as I adjust it to fit the business... Is there a limit to the number of &IF cirteria? I have 15 papers. Thanks again. -Monte ========== "John C" wrote: Assumptions: Tier Table is defined as TblTier The first row of the Tier Table is defined as Tier The Customer's Tier column is defined as CustTier I have your table for customers beginning in row 7, with customer 1 in cell A8, etc. The formula in F8: (rate) =VLOOKUP(CustTier,TblTier,MATCH(IF(C8<"","Paper1" ,"")&IF(D8<"","Paper2","")IF(E8<"","Paper3",""), Tier,0),FALSE) The formula in G8: =SUM(C8:E8)*F8 This also assumes that each customer will only have 1 possibility in either Paper1, Paper2, or Paper3. Multiple entries break it. Hope this helps. -- John C ========== "Monte" wrote: Hello! Life was simple, then we offered multiple rates for the same paper. Your assistance with the following scenario is greatly appreciated. -Monte Rate Chart Tier....Paper1...Paper2...Paper3 ..1.......$1.00.....$5.00.....$7.00 ..2.......$2.00.....$5.00.....$7.00 ..3.......$3.00.....$5.00.....$7.00 ..4.......$4.00.....$5.00.....$7.00 Customer Accounts Customer.....Tier.....Paper1.....Paper2.....Paper3 .....Rate.....Due Customer1......1..........1....................... ................$1.00....$1.00 Customer2......2..........4....................... ................$2.00....$8.00 Customer3......3..........X....................... ................$3.00....$0.00 Customer4......1.........................1........ ................$5.00....$5.00 Customer5......4.................................. ......X.........$7.00....$0.00 When the customer subscribes to paper1, use the customers tier number to find the corresponding rate on the rate chart. Do this even if the customer is not currently receiving the paper (marked with an x). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX / MATCH with SUMIF/COUNTIF/SUMPRODUCT Statements? | Excel Worksheet Functions | |||
vlookup, sumproduct, hlookup, index match, not sure | Excel Discussion (Misc queries) | |||
Help: Vlookup, Index, Match, or Sumproduct? | Excel Worksheet Functions | |||
maybe lookup/index/match/sumproduct | Excel Discussion (Misc queries) | |||
Combine index match and sumproduct | Excel Discussion (Misc queries) |