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

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

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

=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

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

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

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