Sum of 12 cells

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Don Guillett]

Since you asked in functions, will this do it with a formula.
=IF(COUNT(A2:L2)=12,AVERAGE(A2:I2),(AVERAGE(A2:I2)*12))

 

[Guest]

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

 

[T. Valko]

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

http://imageshack.us/

 

[Guest]

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