Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please can you help me
I posted this earlier but managed to confuse Jarek who kindly offered some
solutions, but to no avail. I have 15 sales Categories listed in Column A. Below are just 3 of them. Each Sales category has about 5 different sets of sales ranges (colB) and depending on the sales value a number of rep calls (Col C) is allocated for the year. I am trying to calculate the number of rep calls if in my main table i have in one column the sales category and the sale value Sales Matrix Table Col A Col B (sales Ranges) Col C (No of Rep Calls) Direct Retailer £0 0 Direct Retailer £5000 5 Direct Retailer 10000 10 Direct Retailer 20000 20 Direct Retailer 50000 40 Wholesaler 0 0 Wholesaler 15000 15 Wholesaler 35000 20 Wholesaler 65000 25 Wholesaler 100000 40 Garden Centre 0 2 Garden Centre 5000 5 Garden Centre 7500 10 Garden Centre 10000 20 Garden Centre 20000 25 Now if in my main sales data table in Col A i have Wholesaler, with a sales value of £75K in Col B, i need the result to return 25 Rep Calls in Col C If i was to have say Garden Centre as the category with a sales value of £500, the rep calls should return 2 I really hope this helps explain what i need and that someone can help. The main sales data table is over 5000 rows, so if there is a solution i would be over the moon. Many Thanks -- Matt Lynn Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200911/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please can you help me
Matt via OfficeKB.com wrote:
I posted this earlier but managed to confuse Jarek who kindly offered some solutions, but to no avail. I have 15 sales Categories listed in Column A. Below are just 3 of them. Each Sales category has about 5 different sets of sales ranges (colB) and depending on the sales value a number of rep calls (Col C) is allocated for the year. I am trying to calculate the number of rep calls if in my main table i have in one column the sales category and the sale value Sales Matrix Table Col A Col B (sales Ranges) Col C (No of Rep Calls) Direct Retailer £0 0 Direct Retailer £5000 5 Direct Retailer 10000 10 Direct Retailer 20000 20 Direct Retailer 50000 40 Wholesaler 0 0 Wholesaler 15000 15 Wholesaler 35000 20 Wholesaler 65000 25 Wholesaler 100000 40 Garden Centre 0 2 Garden Centre 5000 5 Garden Centre 7500 10 Garden Centre 10000 20 Garden Centre 20000 25 Now if in my main sales data table in Col A i have Wholesaler, with a sales value of £75K in Col B, i need the result to return 25 Rep Calls in Col C If i was to have say Garden Centre as the category with a sales value of £500, the rep calls should return 2 I really hope this helps explain what i need and that someone can help. The main sales data table is over 5000 rows, so if there is a solution i would be over the moon. Many Thanks Array formula (commit with CTRL+SHIFT+ENTER): =LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$50 00,""),Data!$C$1:$C$5000) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please can you help me
Thanks Glenn, but where does this formaul look at the actual sales value
reached by the customer. I see its looking up the category B1 in a tab called data in col A, and the sales ranges in Col B, and bringing back the rep calls in Col C, but how does it know what mysales actual was Thanks Matt Glenn wrote: I posted this earlier but managed to confuse Jarek who kindly offered some solutions, but to no avail. [quoted text clipped - 34 lines] over the moon. Many Thanks Array formula (commit with CTRL+SHIFT+ENTER): =LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$5 000,""),Data!$C$1:$C$5000) -- Matt Lynn Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please can you help me
You said the the category would be in column A and the sales value would be in
column B. In my formula, I referenced A1 for category and B1 for sales value. =LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$50 00,""),Data!$C$1:$C$5000) ______^^^^____________________^^^^________________ ________________________ Mattlynn via OfficeKB.com wrote: Thanks Glenn, but where does this formaul look at the actual sales value reached by the customer. I see its looking up the category B1 in a tab called data in col A, and the sales ranges in Col B, and bringing back the rep calls in Col C, but how does it know what mysales actual was Thanks Matt Glenn wrote: I posted this earlier but managed to confuse Jarek who kindly offered some solutions, but to no avail. [quoted text clipped - 34 lines] over the moon. Many Thanks Array formula (commit with CTRL+SHIFT+ENTER): =LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$50 00,""),Data!$C$1:$C$5000) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please can you help me
To help clarify what is happening, look at the middle of the formula:
IF( Data!$A$1:$A$5000 = A1 , Data!$B$1:$B$5000 , "" ) If the value in column A (Category) from Data matches the Category entered in cell A1, it returns the value from column B (Sales Range) in Data, otherwise it returns "". Because the formula references multiple cells and was array entered, the result is an array of values and now your formula looks something like this: =LOOKUP(B1, {"", "", "", 0, 15000, 35000, 65000, 10000, "", "", ""}, Data!$C$1:$C$5000) The LOOKUP compares the sales value entered in B1 to the array and returns the corresponding value in column C (Rep Calls) from Data, following the rules of the LOOKUP function. Mattlynn via OfficeKB.com wrote: Thanks Glenn, but where does this formaul look at the actual sales value reached by the customer. I see its looking up the category B1 in a tab called data in col A, and the sales ranges in Col B, and bringing back the rep calls in Col C, but how does it know what mysales actual was Thanks Matt Glenn wrote: I posted this earlier but managed to confuse Jarek who kindly offered some solutions, but to no avail. [quoted text clipped - 34 lines] over the moon. Many Thanks Array formula (commit with CTRL+SHIFT+ENTER): =LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$50 00,""),Data!$C$1:$C$5000) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please can you help me
Hi Glenn
Thanks for all this. I just cant get it to work out, may i send you the spreadsheet for you to have a look for me. Many Thanks Matt Glenn wrote: You said the the category would be in column A and the sales value would be in column B. In my formula, I referenced A1 for category and B1 for sales value. =LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$5 000,""),Data!$C$1:$C$5000) ______^^^^____________________^^^^________________ ________________________ Thanks Glenn, but where does this formaul look at the actual sales value reached by the customer. [quoted text clipped - 14 lines] =LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$50 00,""),Data!$C$1:$C$5000) -- Matt Lynn Message posted via http://www.officekb.com |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please can you help me
Glenn
You're a star - with some slight changing to the referenced cells, i managed to get this to work - you're a bloody clever person Many thanks Matt Glenn wrote: You said the the category would be in column A and the sales value would be in column B. In my formula, I referenced A1 for category and B1 for sales value. =LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$5 000,""),Data!$C$1:$C$5000) ______^^^^____________________^^^^________________ ________________________ Thanks Glenn, but where does this formaul look at the actual sales value reached by the customer. [quoted text clipped - 14 lines] =LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$50 00,""),Data!$C$1:$C$5000) -- Matt Lynn Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200911/1 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please can you help me
Glad you got it working.
Mattlynn via OfficeKB.com wrote: Glenn You're a star - with some slight changing to the referenced cells, i managed to get this to work - you're a bloody clever person Many thanks Matt Glenn wrote: You said the the category would be in column A and the sales value would be in column B. In my formula, I referenced A1 for category and B1 for sales value. =LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$50 00,""),Data!$C$1:$C$5000) ______^^^^____________________^^^^________________ ________________________ Thanks Glenn, but where does this formaul look at the actual sales value reached by the customer. [quoted text clipped - 14 lines] =LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$50 00,""),Data!$C$1:$C$5000) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|