average that includes estimates if data not available
 

I need a formula that totals 12 cells in a row ( the row is monthly sales)
for an annual total.

However if the row doesn't have a full 12 months worth of data I need to
estimate the annual sales by taking an average of the available months and
multiplying them by 12.

(the available months will vary on each row- some may have only the first 3
months or 7 months etc)

Thanks for any help on this.






--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

average that includes estimates if data not available
 

With your months in say B2:M2, then in N2 =SUM(B2:M2). In O2
=COUNTIF(B2:M2,"") to count the number of months with totals, and in P2
=N2/(12-O2)*12
--
Hth

Kassie Kasselman
Change xxx to hotmail


"BillyRogers" wrote:

I need a formula that totals 12 cells in a row ( the row is monthly sales)
for an annual total.

However if the row doesn't have a full 12 months worth of data I need to
estimate the annual sales by taking an average of the available months and
multiplying them by 12.

(the available months will vary on each row- some may have only the first 3
months or 7 months etc)

Thanks for any help on this.






--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

average that includes estimates if data not available
 

One way (assuming the cells without data are blank):

=AVERAGE(A1:L1)*12

In article ,
BillyRogers wrote:

I need a formula that totals 12 cells in a row ( the row is monthly sales)
for an annual total.

However if the row doesn't have a full 12 months worth of data I need to
estimate the annual sales by taking an average of the available months and
multiplying them by 12.

(the available months will vary on each row- some may have only the first 3
months or 7 months etc)

Thanks for any help on this.