Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Column Header
Hi all - I have the following columns and data
My Column headers are Item Code - Item Description and then Week numbers for the year The amount of sales for each item are scattered under the week number - there are some empty cells and some cells contain 0 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Column Header
On Feb 10, 11:06*pm, wrote:
Hi all - I have the following columns and data My Column headers are Item Code - Item Description and then Week numbers for the year The amount of sales for each item are scattered under the week number - there are some empty cells and some cells contain 0 Sorry - didn't finish - so what I want to do is enter a column before the weeks start and in the column pull the column header (week number) for the last cell in the row that is greater than 0 - so if for item "A" the last sales (number greater than 0) is in column G I want to return the value in row 1 of that column - which would be the week number Thanks - Jake |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Column Header
Try this:
Assume column headers are in the range B1:K1 Sales figures in the range B2:K2. There is only numbers or empty cells in this range. =IF(COUNTIF(B2:K2,"0"),LOOKUP(2,1/(B2:K20),B1:K1),"") -- Biff Microsoft Excel MVP wrote in message ... Hi all - I have the following columns and data My Column headers are Item Code - Item Description and then Week numbers for the year The amount of sales for each item are scattered under the week number - there are some empty cells and some cells contain 0 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Column Header
On Feb 10, 11:53*pm, "T. Valko" wrote:
Try this: Assume column headers are in the range B1:K1 Sales figures in the range B2:K2. There is only numbers or empty cells in this range. =IF(COUNTIF(B2:K2,"0"),LOOKUP(2,1/(B2:K20),B1:K1),"") -- Biff Microsoft Excel MVP wrote in message ... Hi all - I have the following columns and data My Column headers are Item Code - Item Description and then Week numbers for the year The amount of sales for each item are scattered under the week number - there are some empty cells and some cells contain 0- Hide quoted text - - Show quoted text - Biff - thanks so much - works perfectly. I'm trying to figure out how it works - I read up on the Lookup function - the part I'm trying to get is the 2,1/(B2:K20) part - why 2 and why 1 divided by the reference. Thanks again - Jake |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Column Header
Here's how it works:
LOOKUP takes these arguments: LOOKUP(lookup_value,lookup_vector,result_vector) In our formula the lookup_value is 2 The lookup_vector is 1/(B2:K20) The result_vector is B1:K1 This means we want to find the value 2 in the lookup_vector and return the corresponding value from the result_vector. This portion of the lookup_vector: (B2:K20) Will return an array of either TRUE or FALSE. Dividing those logical values by 1: 1/(B2:K20) Will result in an array of either 1 or a #DIV/0! error 1/(TRUE) = 1 1/(FALSE) = #DIV/0! The way that LOOKUP works is if the lookup_value is greater than all the values in the lookup_vector it will match the *last* value in the lookup_vector that is less than the lookup_value. That's where the lookup_value of 2 comes into play. Since our lookup_vector comprises an array of 1s and #DIV errors the *LAST* value that is less than the lookup_value has to be a 1. So, the formula returns the header that corresponds to the *last* 1 in the lookup_vector. -- Biff Microsoft Excel MVP wrote in message ... On Feb 10, 11:53 pm, "T. Valko" wrote: Try this: Assume column headers are in the range B1:K1 Sales figures in the range B2:K2. There is only numbers or empty cells in this range. =IF(COUNTIF(B2:K2,"0"),LOOKUP(2,1/(B2:K20),B1:K1),"") -- Biff Microsoft Excel MVP wrote in message ... Hi all - I have the following columns and data My Column headers are Item Code - Item Description and then Week numbers for the year The amount of sales for each item are scattered under the week number - there are some empty cells and some cells contain 0- Hide quoted text - - Show quoted text - Biff - thanks so much - works perfectly. I'm trying to figure out how it works - I read up on the Lookup function - the part I'm trying to get is the 2,1/(B2:K20) part - why 2 and why 1 divided by the reference. Thanks again - Jake |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get Intersection value given column header and row header | Excel Discussion (Misc queries) | |||
Trouble returning to Normal view after inserting a header and foot | Excel Worksheet Functions | |||
Returning column header as result | Excel Worksheet Functions | |||
Excel - returning column headers in a seperate column | Excel Discussion (Misc queries) | |||
returning header row as a result... | Excel Worksheet Functions |