Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total 12 columns starting with first non blank column
I'm trying to write a formula that will add 12 columns together starting with
the first non blank column(and adding the following 11 columns). The start column won't necessarily be the same column for each row. I've found some array formulas that give the the sequential column number where the data starts =MATCH(TRUE,H5:AE5<"",0)+6 this returns a number though and not a column letter of the first non blank cell. I'm not sure if this is the approach to take or not. I'm trying to add up sales data for different businesses for their first 12 months regardless of when they which isn't the same for all companies. the data in spreadsheet looks something like this. 20 55 55 55 55 55 20 55 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 20 55 55 55 20 55 20 55 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total 12 columns starting with first non blank column
the data I posted didn't show up correctly
let me try again here.....I'll put a zero where the blanks are just as a placeholder jan feb mar apr may jun jul aug sept oct nov etc 55 55 55 22 55 11 555 12 11 11 11 0 11 11 11 11 11 1 11 11 11 12 0 0 0 11 11 11 1 11 11 11 12 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: I'm trying to write a formula that will add 12 columns together starting with the first non blank column(and adding the following 11 columns). The start column won't necessarily be the same column for each row. I've found some array formulas that give the the sequential column number where the data starts =MATCH(TRUE,H5:AE5<"",0)+6 this returns a number though and not a column letter of the first non blank cell. I'm not sure if this is the approach to take or not. I'm trying to add up sales data for different businesses for their first 12 months regardless of when they which isn't the same for all companies. the data in spreadsheet looks something like this. 20 55 55 55 55 55 20 55 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 20 55 55 55 20 55 20 55 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total 12 columns starting with first non blank column
What I'm actually trying to find is the cell address of the first non-empty
cell in a row. This formula gives me the the position withing the range of the first non empty cell but not the cell address =MATCH(TRUE,H16:AB16<"",0) it simply returns and interger such as 4 when the fourth cell in the range is the first non empty cell. somehow I need to take that and convert it into a cell address Thanks, -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: the data I posted didn't show up correctly let me try again here.....I'll put a zero where the blanks are just as a placeholder jan feb mar apr may jun jul aug sept oct nov etc 55 55 55 22 55 11 555 12 11 11 11 0 11 11 11 11 11 1 11 11 11 12 0 0 0 11 11 11 1 11 11 11 12 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: I'm trying to write a formula that will add 12 columns together starting with the first non blank column(and adding the following 11 columns). The start column won't necessarily be the same column for each row. I've found some array formulas that give the the sequential column number where the data starts =MATCH(TRUE,H5:AE5<"",0)+6 this returns a number though and not a column letter of the first non blank cell. I'm not sure if this is the approach to take or not. I'm trying to add up sales data for different businesses for their first 12 months regardless of when they which isn't the same for all companies. the data in spreadsheet looks something like this. 20 55 55 55 55 55 20 55 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 20 55 55 55 20 55 20 55 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total 12 columns starting with first non blank column
=CELL("address",INDEX(H16:AB16,MATCH(TRUE,H16:AB16 <"",0)))
You could also use the address function -- Regards, Peo Sjoblom "BillyRogers" wrote in message ... What I'm actually trying to find is the cell address of the first non-empty cell in a row. This formula gives me the the position withing the range of the first non empty cell but not the cell address =MATCH(TRUE,H16:AB16<"",0) it simply returns and interger such as 4 when the fourth cell in the range is the first non empty cell. somehow I need to take that and convert it into a cell address Thanks, -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: the data I posted didn't show up correctly let me try again here.....I'll put a zero where the blanks are just as a placeholder jan feb mar apr may jun jul aug sept oct nov etc 55 55 55 22 55 11 555 12 11 11 11 0 11 11 11 11 11 1 11 11 11 12 0 0 0 11 11 11 1 11 11 11 12 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: I'm trying to write a formula that will add 12 columns together starting with the first non blank column(and adding the following 11 columns). The start column won't necessarily be the same column for each row. I've found some array formulas that give the the sequential column number where the data starts =MATCH(TRUE,H5:AE5<"",0)+6 this returns a number though and not a column letter of the first non blank cell. I'm not sure if this is the approach to take or not. I'm trying to add up sales data for different businesses for their first 12 months regardless of when they which isn't the same for all companies. the data in spreadsheet looks something like this. 20 55 55 55 55 55 20 55 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 20 55 55 55 20 55 20 55 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total 12 columns starting with first non blank column
Thanks....
is there a way to change that so that it adds the cell returned by that formula and the next 11 columns? -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "Peo Sjoblom" wrote: =CELL("address",INDEX(H16:AB16,MATCH(TRUE,H16:AB16 <"",0))) You could also use the address function -- Regards, Peo Sjoblom "BillyRogers" wrote in message ... What I'm actually trying to find is the cell address of the first non-empty cell in a row. This formula gives me the the position withing the range of the first non empty cell but not the cell address =MATCH(TRUE,H16:AB16<"",0) it simply returns and interger such as 4 when the fourth cell in the range is the first non empty cell. somehow I need to take that and convert it into a cell address Thanks, -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: the data I posted didn't show up correctly let me try again here.....I'll put a zero where the blanks are just as a placeholder jan feb mar apr may jun jul aug sept oct nov etc 55 55 55 22 55 11 555 12 11 11 11 0 11 11 11 11 11 1 11 11 11 12 0 0 0 11 11 11 1 11 11 11 12 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: I'm trying to write a formula that will add 12 columns together starting with the first non blank column(and adding the following 11 columns). The start column won't necessarily be the same column for each row. I've found some array formulas that give the the sequential column number where the data starts =MATCH(TRUE,H5:AE5<"",0)+6 this returns a number though and not a column letter of the first non blank cell. I'm not sure if this is the approach to take or not. I'm trying to add up sales data for different businesses for their first 12 months regardless of when they which isn't the same for all companies. the data in spreadsheet looks something like this. 20 55 55 55 55 55 20 55 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 20 55 55 55 20 55 20 55 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total 12 columns starting with first non blank column
One way
=SUM(OFFSET($H$16,,MATCH(TRUE,H16:AB16<"",0)-1,,12)) -- Regards, Peo Sjoblom "BillyRogers" wrote in message ... Thanks.... is there a way to change that so that it adds the cell returned by that formula and the next 11 columns? -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "Peo Sjoblom" wrote: =CELL("address",INDEX(H16:AB16,MATCH(TRUE,H16:AB16 <"",0))) You could also use the address function -- Regards, Peo Sjoblom "BillyRogers" wrote in message ... What I'm actually trying to find is the cell address of the first non-empty cell in a row. This formula gives me the the position withing the range of the first non empty cell but not the cell address =MATCH(TRUE,H16:AB16<"",0) it simply returns and interger such as 4 when the fourth cell in the range is the first non empty cell. somehow I need to take that and convert it into a cell address Thanks, -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: the data I posted didn't show up correctly let me try again here.....I'll put a zero where the blanks are just as a placeholder jan feb mar apr may jun jul aug sept oct nov etc 55 55 55 22 55 11 555 12 11 11 11 0 11 11 11 11 11 1 11 11 11 12 0 0 0 11 11 11 1 11 11 11 12 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: I'm trying to write a formula that will add 12 columns together starting with the first non blank column(and adding the following 11 columns). The start column won't necessarily be the same column for each row. I've found some array formulas that give the the sequential column number where the data starts =MATCH(TRUE,H5:AE5<"",0)+6 this returns a number though and not a column letter of the first non blank cell. I'm not sure if this is the approach to take or not. I'm trying to add up sales data for different businesses for their first 12 months regardless of when they which isn't the same for all companies. the data in spreadsheet looks something like this. 20 55 55 55 55 55 20 55 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 20 55 55 55 20 55 20 55 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total 12 columns starting with first non blank column
A little<bg longer, but just as an exercise, one NON-volatile way would be
this *array* formula: =SUM(INDEX(H16:AB16,MATCH(TRUE,H16:AB16<"",0)): INDEX(H16:AB16,MATCH(TRUE,H16:AB16<"",0)+11)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. *OR* A NON-volatile *AND* NON-array way: =SUM(INDEX(H16:AB16,MATCH(1,INDEX(--(ISNUMBER(H16:AB16)),1,),0)): INDEX(H16:AB16,MATCH(1,INDEX(--(ISNUMBER(H16:AB16)),1,),0)+11)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "BillyRogers" wrote in message ... Thanks.... is there a way to change that so that it adds the cell returned by that formula and the next 11 columns? -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "Peo Sjoblom" wrote: =CELL("address",INDEX(H16:AB16,MATCH(TRUE,H16:AB16 <"",0))) You could also use the address function -- Regards, Peo Sjoblom "BillyRogers" wrote in message ... What I'm actually trying to find is the cell address of the first non-empty cell in a row. This formula gives me the the position withing the range of the first non empty cell but not the cell address =MATCH(TRUE,H16:AB16<"",0) it simply returns and interger such as 4 when the fourth cell in the range is the first non empty cell. somehow I need to take that and convert it into a cell address Thanks, -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: the data I posted didn't show up correctly let me try again here.....I'll put a zero where the blanks are just as a placeholder jan feb mar apr may jun jul aug sept oct nov etc 55 55 55 22 55 11 555 12 11 11 11 0 11 11 11 11 11 1 11 11 11 12 0 0 0 11 11 11 1 11 11 11 12 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: I'm trying to write a formula that will add 12 columns together starting with the first non blank column(and adding the following 11 columns). The start column won't necessarily be the same column for each row. I've found some array formulas that give the the sequential column number where the data starts =MATCH(TRUE,H5:AE5<"",0)+6 this returns a number though and not a column letter of the first non blank cell. I'm not sure if this is the approach to take or not. I'm trying to add up sales data for different businesses for their first 12 months regardless of when they which isn't the same for all companies. the data in spreadsheet looks something like this. 20 55 55 55 55 55 20 55 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 20 55 55 55 20 55 20 55 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total 12 columns starting with first non blank column
Note that you will get a ref error if for instance the first value starts in
R16 and beyond since it less than 12 columns left. I actually did the same formula as an alternative but when I got an error I didn't want to spend anymore time offsetting the formula to adapt for less than 12 remaining columns which obviously is possible -- Regards, Peo Sjoblom "RagDyer" wrote in message ... A little<bg longer, but just as an exercise, one NON-volatile way would be this *array* formula: =SUM(INDEX(H16:AB16,MATCH(TRUE,H16:AB16<"",0)): INDEX(H16:AB16,MATCH(TRUE,H16:AB16<"",0)+11)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. *OR* A NON-volatile *AND* NON-array way: =SUM(INDEX(H16:AB16,MATCH(1,INDEX(--(ISNUMBER(H16:AB16)),1,),0)): INDEX(H16:AB16,MATCH(1,INDEX(--(ISNUMBER(H16:AB16)),1,),0)+11)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "BillyRogers" wrote in message ... Thanks.... is there a way to change that so that it adds the cell returned by that formula and the next 11 columns? -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "Peo Sjoblom" wrote: =CELL("address",INDEX(H16:AB16,MATCH(TRUE,H16:AB16 <"",0))) You could also use the address function -- Regards, Peo Sjoblom "BillyRogers" wrote in message ... What I'm actually trying to find is the cell address of the first non-empty cell in a row. This formula gives me the the position withing the range of the first non empty cell but not the cell address =MATCH(TRUE,H16:AB16<"",0) it simply returns and interger such as 4 when the fourth cell in the range is the first non empty cell. somehow I need to take that and convert it into a cell address Thanks, -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: the data I posted didn't show up correctly let me try again here.....I'll put a zero where the blanks are just as a placeholder jan feb mar apr may jun jul aug sept oct nov etc 55 55 55 22 55 11 555 12 11 11 11 0 11 11 11 11 11 1 11 11 11 12 0 0 0 11 11 11 1 11 11 11 12 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: I'm trying to write a formula that will add 12 columns together starting with the first non blank column(and adding the following 11 columns). The start column won't necessarily be the same column for each row. I've found some array formulas that give the the sequential column number where the data starts =MATCH(TRUE,H5:AE5<"",0)+6 this returns a number though and not a column letter of the first non blank cell. I'm not sure if this is the approach to take or not. I'm trying to add up sales data for different businesses for their first 12 months regardless of when they which isn't the same for all companies. the data in spreadsheet looks something like this. 20 55 55 55 55 55 20 55 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 20 55 55 55 20 55 20 55 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total 12 columns starting with first non blank column
Never thought to test for that Peo.
The one shortcoming of the Offset formula though, is that it calculates *beyond* the stated range of interest, Whether or not that presents a problem to the OP is questionable. But ... for the sake of the archives, and to just complete the "experience", the following 2 formulas take into consideration your concern of the first value being entered in a cell where *less* then 12 columns remain in the calculating referenced range: *Array* and NON-volatile: =SUM(INDEX(H16:AB16,MATCH(TRUE,H16:AB16<"",0)): INDEX(H16:AB16,MIN(21,MATCH(TRUE,H16:AB16<"",0)+1 1))) Non-array *AND* NON-volatile: =SUM(INDEX(H16:AB16,MATCH(1,INDEX(--(ISNUMBER(H16:AB16)),1,),0)): INDEX(H16:AB16,MIN(21,MATCH(1,INDEX(--(ISNUMBER(H16:AB16)),1,),0)+11))) -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... Note that you will get a ref error if for instance the first value starts in R16 and beyond since it less than 12 columns left. I actually did the same formula as an alternative but when I got an error I didn't want to spend anymore time offsetting the formula to adapt for less than 12 remaining columns which obviously is possible -- Regards, Peo Sjoblom "RagDyer" wrote in message ... A little<bg longer, but just as an exercise, one NON-volatile way would be this *array* formula: =SUM(INDEX(H16:AB16,MATCH(TRUE,H16:AB16<"",0)): INDEX(H16:AB16,MATCH(TRUE,H16:AB16<"",0)+11)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. *OR* A NON-volatile *AND* NON-array way: =SUM(INDEX(H16:AB16,MATCH(1,INDEX(--(ISNUMBER(H16:AB16)),1,),0)): INDEX(H16:AB16,MATCH(1,INDEX(--(ISNUMBER(H16:AB16)),1,),0)+11)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "BillyRogers" wrote in message ... Thanks.... is there a way to change that so that it adds the cell returned by that formula and the next 11 columns? -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "Peo Sjoblom" wrote: =CELL("address",INDEX(H16:AB16,MATCH(TRUE,H16:AB16 <"",0))) You could also use the address function -- Regards, Peo Sjoblom "BillyRogers" wrote in message ... What I'm actually trying to find is the cell address of the first non-empty cell in a row. This formula gives me the the position withing the range of the first non empty cell but not the cell address =MATCH(TRUE,H16:AB16<"",0) it simply returns and interger such as 4 when the fourth cell in the range is the first non empty cell. somehow I need to take that and convert it into a cell address Thanks, -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: the data I posted didn't show up correctly let me try again here.....I'll put a zero where the blanks are just as a placeholder jan feb mar apr may jun jul aug sept oct nov etc 55 55 55 22 55 11 555 12 11 11 11 0 11 11 11 11 11 1 11 11 11 12 0 0 0 11 11 11 1 11 11 11 12 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: I'm trying to write a formula that will add 12 columns together starting with the first non blank column(and adding the following 11 columns). The start column won't necessarily be the same column for each row. I've found some array formulas that give the the sequential column number where the data starts =MATCH(TRUE,H5:AE5<"",0)+6 this returns a number though and not a column letter of the first non blank cell. I'm not sure if this is the approach to take or not. I'm trying to add up sales data for different businesses for their first 12 months regardless of when they which isn't the same for all companies. the data in spreadsheet looks something like this. 20 55 55 55 55 55 20 55 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 20 55 55 55 20 55 20 55 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total 12 columns starting with first non blank column
how can I modify this so that i get a formula that totals the second group of
12 cells and another that does the same for the 3rd group of 12 cells? Does that make sense? there can be up to 36 cells of data in the row. thanks, -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "RagDyeR" wrote: Never thought to test for that Peo. The one shortcoming of the Offset formula though, is that it calculates *beyond* the stated range of interest, Whether or not that presents a problem to the OP is questionable. But ... for the sake of the archives, and to just complete the "experience", the following 2 formulas take into consideration your concern of the first value being entered in a cell where *less* then 12 columns remain in the calculating referenced range: *Array* and NON-volatile: =SUM(INDEX(H16:AB16,MATCH(TRUE,H16:AB16<"",0)): INDEX(H16:AB16,MIN(21,MATCH(TRUE,H16:AB16<"",0)+1 1))) Non-array *AND* NON-volatile: =SUM(INDEX(H16:AB16,MATCH(1,INDEX(--(ISNUMBER(H16:AB16)),1,),0)): INDEX(H16:AB16,MIN(21,MATCH(1,INDEX(--(ISNUMBER(H16:AB16)),1,),0)+11))) -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... Note that you will get a ref error if for instance the first value starts in R16 and beyond since it less than 12 columns left. I actually did the same formula as an alternative but when I got an error I didn't want to spend anymore time offsetting the formula to adapt for less than 12 remaining columns which obviously is possible -- Regards, Peo Sjoblom "RagDyer" wrote in message ... A little<bg longer, but just as an exercise, one NON-volatile way would be this *array* formula: =SUM(INDEX(H16:AB16,MATCH(TRUE,H16:AB16<"",0)): INDEX(H16:AB16,MATCH(TRUE,H16:AB16<"",0)+11)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. *OR* A NON-volatile *AND* NON-array way: =SUM(INDEX(H16:AB16,MATCH(1,INDEX(--(ISNUMBER(H16:AB16)),1,),0)): INDEX(H16:AB16,MATCH(1,INDEX(--(ISNUMBER(H16:AB16)),1,),0)+11)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "BillyRogers" wrote in message ... Thanks.... is there a way to change that so that it adds the cell returned by that formula and the next 11 columns? -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "Peo Sjoblom" wrote: =CELL("address",INDEX(H16:AB16,MATCH(TRUE,H16:AB16 <"",0))) You could also use the address function -- Regards, Peo Sjoblom "BillyRogers" wrote in message ... What I'm actually trying to find is the cell address of the first non-empty cell in a row. This formula gives me the the position withing the range of the first non empty cell but not the cell address =MATCH(TRUE,H16:AB16<"",0) it simply returns and interger such as 4 when the fourth cell in the range is the first non empty cell. somehow I need to take that and convert it into a cell address Thanks, -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: the data I posted didn't show up correctly let me try again here.....I'll put a zero where the blanks are just as a placeholder jan feb mar apr may jun jul aug sept oct nov etc 55 55 55 22 55 11 555 12 11 11 11 0 11 11 11 11 11 1 11 11 11 12 0 0 0 11 11 11 1 11 11 11 12 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 "BillyRogers" wrote: I'm trying to write a formula that will add 12 columns together starting with the first non blank column(and adding the following 11 columns). The start column won't necessarily be the same column for each row. I've found some array formulas that give the the sequential column number where the data starts =MATCH(TRUE,H5:AE5<"",0)+6 this returns a number though and not a column letter of the first non blank cell. I'm not sure if this is the approach to take or not. I'm trying to add up sales data for different businesses for their first 12 months regardless of when they which isn't the same for all companies. the data in spreadsheet looks something like this. 20 55 55 55 55 55 20 55 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 55 20 55 55 55 20 55 55 55 20 55 20 55 -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Total column changes colors when total equals sum of other columns | New Users to Excel | |||
Only count columns if the column next to it is not blank | Excel Discussion (Misc queries) | |||
Setting column width for blank columns | Excel Discussion (Misc queries) | |||
extract data Column 1 starting with A1, A4, A7, A10, etc. | Excel Worksheet Functions | |||
in excel just want to add 4 columns and total in next column | Excel Worksheet Functions |