Sum of 12 cells
 

I need three formulas

1. Will find the first 12 non-empty cells in the range (a single row) and
sum them, if there are not 12 non-empty cells it will average the non-empty
cells and multiply by 12.

2. Will find the next 12 non-empty cells (13-24) and add them. If there are
not 12 non- empty cells in this second group, average them and multiply by 12.

3. Will find the next 12 non-empty cells (25-36) and add them, if there
arent 12 non-empty cells average the non-empty cells and multiply by 12.

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

Sum of 12 cells
 

You say you want to find the first 12 non-empty cells in a row, but if there
are not 12, then average etc, thereafter repeat this excercise another two
times. However if you find the first 12 non-empty cells, then there cannot
be empty cells?

Are you actually saying that you want a formula that will sum 12 adjacent
cells, on conditions that they are all not empty, else average these 12 cells
and multiply the result by 12. Iow, sum(C12:N12) if all are not empty. If
there are empty cells in C12:N12, then average the non empty cells in C12:N12
and multiply by 12.

--
Hth

Kassie Kasselman
Change xxx to hotmail


"BillyRogers" wrote:

I need three formulas

1. Will find the first 12 non-empty cells in the range (a single row) and
sum them, if there are not 12 non-empty cells it will average the non-empty
cells and multiply by 12.

2. Will find the next 12 non-empty cells (13-24) and add them. If there are
not 12 non- empty cells in this second group, average them and multiply by 12.

3. Will find the next 12 non-empty cells (25-36) and add them, if there
arent 12 non-empty cells average the non-empty cells and multiply by 12.

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

Sum of 12 cells
 

kassie,

I need to use the formula on data that doesn't always start on the same
column. There can be up to 3 years (36 months worth of data in the row-but
not more)

The sales data starts in different columns(months) - they don't all start on
the same months. the data will be consecutive that is there won't be cells
with sales data then empty cells followed by more sales data.

ex
10 11 12 11 09 07
09 11 12 13 15 13 11 12 11
11 11 11 12 11 12 12 12 12
11 11 11 11
etc.


--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003


"kassie" wrote:

You say you want to find the first 12 non-empty cells in a row, but if there
are not 12, then average etc, thereafter repeat this excercise another two
times. However if you find the first 12 non-empty cells, then there cannot
be empty cells?

Are you actually saying that you want a formula that will sum 12 adjacent
cells, on conditions that they are all not empty, else average these 12 cells
and multiply the result by 12. Iow, sum(C12:N12) if all are not empty. If
there are empty cells in C12:N12, then average the non empty cells in C12:N12
and multiply by 12.

--
Hth

Kassie Kasselman
Change xxx to hotmail


"BillyRogers" wrote:

I need three formulas

1. Will find the first 12 non-empty cells in the range (a single row) and
sum them, if there are not 12 non-empty cells it will average the non-empty
cells and multiply by 12.

2. Will find the next 12 non-empty cells (13-24) and add them. If there are
not 12 non- empty cells in this second group, average them and multiply by 12.

3. Will find the next 12 non-empty cells (25-36) and add them, if there
arent 12 non-empty cells average the non-empty cells and multiply by 12.

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

Sum of 12 cells
 

ok that example didn't turn out right...it looks like the beginning spaces
were removed.

the difficult part is that i don't know which column the data will start in
and not all rows will have data starting in the same column - it depends on
when that particular store was opened.
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003


"BillyRogers" wrote:

kassie,

I need to use the formula on data that doesn't always start on the same
column. There can be up to 3 years (36 months worth of data in the row-but
not more)

The sales data starts in different columns(months) - they don't all start on
the same months. the data will be consecutive that is there won't be cells
with sales data then empty cells followed by more sales data.

ex
10 11 12 11 09 07
09 11 12 13 15 13 11 12 11
11 11 11 12 11 12 12 12 12
11 11 11 11
etc.


--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003


"kassie" wrote:

You say you want to find the first 12 non-empty cells in a row, but if there
are not 12, then average etc, thereafter repeat this excercise another two
times. However if you find the first 12 non-empty cells, then there cannot
be empty cells?

Are you actually saying that you want a formula that will sum 12 adjacent
cells, on conditions that they are all not empty, else average these 12 cells
and multiply the result by 12. Iow, sum(C12:N12) if all are not empty. If
there are empty cells in C12:N12, then average the non empty cells in C12:N12
and multiply by 12.

--
Hth

Kassie Kasselman
Change xxx to hotmail


"BillyRogers" wrote:

I need three formulas

1. Will find the first 12 non-empty cells in the range (a single row) and
sum them, if there are not 12 non-empty cells it will average the non-empty
cells and multiply by 12.

2. Will find the next 12 non-empty cells (13-24) and add them. If there are
not 12 non- empty cells in this second group, average them and multiply by 12.

3. Will find the next 12 non-empty cells (25-36) and add them, if there
arent 12 non-empty cells average the non-empty cells and multiply by 12.

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

Sum of 12 cells
 

Since you asked in functions, will this do it with a formula.
=IF(COUNT(A2:L2)=12,AVERAGE(A2:I2),(AVERAGE(A2:I2) *12))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BillyRogers" wrote in message
...
I need three formulas

1. Will find the first 12 non-empty cells in the range (a single row) and
sum them, if there are not 12 non-empty cells it will average the
non-empty
cells and multiply by 12.

2. Will find the next 12 non-empty cells (13-24) and add them. If there
are
not 12 non- empty cells in this second group, average them and multiply by
12.

3. Will find the next 12 non-empty cells (25-36) and add them, if there
arent 12 non-empty cells average the non-empty cells and multiply by 12.

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

Sum of 12 cells
 

Thanks for the help Don, but I need a formula that can "figure out" which
column to start in. (hence I used the phrase "find the first non-empty cell"
in the range.

Suppose that all the data will be in a given range say columns 10-50. Not
all rows will start on the same columns (which represent months). There can
be up to 36 columns (months) of data.

What the data represents is store sales. What I need is three annual totals
for these stores. Some stores don't have a full 36 months worth of data.
Also, they didn't all start in the same month(column).

It's kind of confusing to explain. If I could attacha screen shot it would
make it much easier to understand.
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003


"Don Guillett" wrote:

Since you asked in functions, will this do it with a formula.
=IF(COUNT(A2:L2)=12,AVERAGE(A2:I2),(AVERAGE(A2:I2) *12))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BillyRogers" wrote in message
...
I need three formulas

1. Will find the first 12 non-empty cells in the range (a single row) and
sum them, if there are not 12 non-empty cells it will average the
non-empty
cells and multiply by 12.

2. Will find the next 12 non-empty cells (13-24) and add them. If there
are
not 12 non- empty cells in this second group, average them and multiply by
12.

3. Will find the next 12 non-empty cells (25-36) and add them, if there
arent 12 non-empty cells average the non-empty cells and multiply by 12.

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

Sum of 12 cells
 

You can upload a screencap here (it's free) and then post a link to the
screencap:

http://imageshack.us/


--
Biff
Microsoft Excel MVP


"BillyRogers" wrote in message
...
Thanks for the help Don, but I need a formula that can "figure out" which
column to start in. (hence I used the phrase "find the first non-empty
cell"
in the range.

Suppose that all the data will be in a given range say columns 10-50.
Not
all rows will start on the same columns (which represent months). There
can
be up to 36 columns (months) of data.

What the data represents is store sales. What I need is three annual
totals
for these stores. Some stores don't have a full 36 months worth of data.
Also, they didn't all start in the same month(column).

It's kind of confusing to explain. If I could attacha screen shot it
would
make it much easier to understand.
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003


"Don Guillett" wrote:

Since you asked in functions, will this do it with a formula.
=IF(COUNT(A2:L2)=12,AVERAGE(A2:I2),(AVERAGE(A2:I2) *12))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BillyRogers" wrote in message
...
I need three formulas

1. Will find the first 12 non-empty cells in the range (a single row)
and
sum them, if there are not 12 non-empty cells it will average the
non-empty
cells and multiply by 12.

2. Will find the next 12 non-empty cells (13-24) and add them. If
there
are
not 12 non- empty cells in this second group, average them and multiply
by
12.

3. Will find the next 12 non-empty cells (25-36) and add them, if there
aren't 12 non-empty cells average the non-empty cells and multiply by
12.

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

Sum of 12 cells
 

Ok thanks, I'll do that tomorrow. have to go now.
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003


"T. Valko" wrote:

You can upload a screencap here (it's free) and then post a link to the
screencap:

http://imageshack.us/


--
Biff
Microsoft Excel MVP


"BillyRogers" wrote in message
...
Thanks for the help Don, but I need a formula that can "figure out" which
column to start in. (hence I used the phrase "find the first non-empty
cell"
in the range.

Suppose that all the data will be in a given range say columns 10-50.
Not
all rows will start on the same columns (which represent months). There
can
be up to 36 columns (months) of data.

What the data represents is store sales. What I need is three annual
totals
for these stores. Some stores don't have a full 36 months worth of data.
Also, they didn't all start in the same month(column).

It's kind of confusing to explain. If I could attacha screen shot it
would
make it much easier to understand.
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003


"Don Guillett" wrote:

Since you asked in functions, will this do it with a formula.
=IF(COUNT(A2:L2)=12,AVERAGE(A2:I2),(AVERAGE(A2:I2) *12))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BillyRogers" wrote in message
...
I need three formulas

1. Will find the first 12 non-empty cells in the range (a single row)
and
sum them, if there are not 12 non-empty cells it will average the
non-empty
cells and multiply by 12.

2. Will find the next 12 non-empty cells (13-24) and add them. If
there
are
not 12 non- empty cells in this second group, average them and multiply
by
12.

3. Will find the next 12 non-empty cells (25-36) and add them, if there
aren't 12 non-empty cells average the non-empty cells and multiply by
12.

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003