I have a large worksheet of sales data where products are down the rows and
months are across columns. I would like a formula to calculate the average
monthly sales but only inlude those months starting with the month of first
sale going forward. If a sale month is zero the cell is blank Example
worksheet:

Mo1 / Mo2 / Mo3 / Mo4 / Mo 5
Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months)
Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of zero)
Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of
zero)
etc

Any ideas? Thanks, Jim.

If the cell "...contains text, logical values, or empty cells, those values
are ignored; however, cells with the value zero are included."

So the quick fix is to insert zeros for the blanks to be included (eg mo 2
and 5 for product 3 in your example -- product 2 works correctly anyway).

You could do this easily by formula: create a second worksheet by copying
the first. Then insert formulas to copy the data from the first worksheet:
if the cell contains a number, use it; else if the cell to its left contains
a number, use 0; else insert blank. Eg if the data starts on sheet 1 at cell
B2, then on sheet 2 cell B2 use

=IF(Sheet1!B20,Sheet1!B2,IF(Sheet1!A2<"",0,""))




"Jim" wrote in message
...
I have a large worksheet of sales data where products are down the rows and
months are across columns. I would like a formula to calculate the
average
monthly sales but only inlude those months starting with the month of
first
sale going forward. If a sale month is zero the cell is blank Example
worksheet:

Mo1 / Mo2 / Mo3 / Mo4 / Mo 5
Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months)
Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of
zero)
Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of
zero)
etc

Any ideas? Thanks, Jim.

Can't you just average each row, average does not include blanks as opposed
to zeros and
from your example it looks like the cells are empty

--
Regards,

Peo Sjoblom

(No private emails please)


"Jim" wrote in message
...
I have a large worksheet of sales data where products are down the rows and
months are across columns. I would like a formula to calculate the
average
monthly sales but only inlude those months starting with the month of
first
sale going forward. If a sale month is zero the cell is blank Example
worksheet:

Mo1 / Mo2 / Mo3 / Mo4 / Mo 5
Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months)
Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of
zero)
Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of
zero)
etc

Any ideas? Thanks, Jim.

But Jim needs to have mo2 and mo5 treated as 0's in product 3.

If I were doing this, I'd put 0's where I need 0's and N/A in the cells that
didn't apply.

But that doesn't get YOU off the hook!



Peo Sjoblom wrote:

Can't you just average each row, average does not include blanks as opposed
to zeros and
from your example it looks like the cells are empty

--
Regards,

Peo Sjoblom

(No private emails please)

"Jim" wrote in message
...
I have a large worksheet of sales data where products are down the rows and
months are across columns. I would like a formula to calculate the
average
monthly sales but only inlude those months starting with the month of
first
sale going forward. If a sale month is zero the cell is blank Example
worksheet:

Mo1 / Mo2 / Mo3 / Mo4 / Mo 5
Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months)
Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of
zero)
Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of
zero)
etc

Any ideas? Thanks, Jim.

--

Dave Peterson

This worked ok for me (until Peo comes back with a prettier response!):

=SUM(B2:F2)/(6-MATCH(TRUE,B2:F2<"",0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Maybe better if all the months could be empty:
=IF(COUNT(B2:F2)=0,"No data",SUM(B2:F2)/(6-MATCH(TRUE,B2:F2<"",0)))

(Still an array formula.)


Jim wrote:

I have a large worksheet of sales data where products are down the rows and
months are across columns. I would like a formula to calculate the average
monthly sales but only inlude those months starting with the month of first
sale going forward. If a sale month is zero the cell is blank Example
worksheet:

Mo1 / Mo2 / Mo3 / Mo4 / Mo 5
Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months)
Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of zero)
Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of
zero)
etc

Any ideas? Thanks, Jim.

--

Dave Peterson

Aha

--
Regards,

Peo Sjoblom

(No private emails please)


"Dave Peterson" wrote in message
...
But Jim needs to have mo2 and mo5 treated as 0's in product 3.

If I were doing this, I'd put 0's where I need 0's and N/A in the cells
that
didn't apply.

But that doesn't get YOU off the hook!



Peo Sjoblom wrote:

Can't you just average each row, average does not include blanks as
opposed
to zeros and
from your example it looks like the cells are empty

--
Regards,

Peo Sjoblom

(No private emails please)

"Jim" wrote in message
...
I have a large worksheet of sales data where products are down the rows
and
months are across columns. I would like a formula to calculate the
average
monthly sales but only inlude those months starting with the month of
first
sale going forward. If a sale month is zero the cell is blank Example
worksheet:

Mo1 / Mo2 / Mo3 / Mo4 / Mo 5
Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all
months)
Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of
zero)
Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5
of
zero)
etc

Any ideas? Thanks, Jim.

--

Dave Peterson

That is pretty enough

--
Regards,

Peo Sjoblom

(No private emails please)


"Dave Peterson" wrote in message
...
This worked ok for me (until Peo comes back with a prettier response!):

=SUM(B2:F2)/(6-MATCH(TRUE,B2:F2<"",0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

Maybe better if all the months could be empty:
=IF(COUNT(B2:F2)=0,"No data",SUM(B2:F2)/(6-MATCH(TRUE,B2:F2<"",0)))

(Still an array formula.)


Jim wrote:

I have a large worksheet of sales data where products are down the rows
and
months are across columns. I would like a formula to calculate the
average
monthly sales but only inlude those months starting with the month of
first
sale going forward. If a sale month is zero the cell is blank Example
worksheet:

Mo1 / Mo2 / Mo3 / Mo4 / Mo 5
Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months)
Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of
zero)
Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5
of
zero)
etc

Any ideas? Thanks, Jim.

--

Dave Peterson

Awesome! Thanks Dave, this is exactly the solution I needed. Thank you for
taking the time to look into this and to share your expertise.

Regards, Jim

"Dave Peterson" wrote:

This worked ok for me (until Peo comes back with a prettier response!):

=SUM(B2:F2)/(6-MATCH(TRUE,B2:F2<"",0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Maybe better if all the months could be empty:
=IF(COUNT(B2:F2)=0,"No data",SUM(B2:F2)/(6-MATCH(TRUE,B2:F2<"",0)))

(Still an array formula.)


Jim wrote:

I have a large worksheet of sales data where products are down the rows and
months are across columns. I would like a formula to calculate the average
monthly sales but only inlude those months starting with the month of first
sale going forward. If a sale month is zero the cell is blank Example
worksheet:

Mo1 / Mo2 / Mo3 / Mo4 / Mo 5
Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months)
Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of zero)
Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of
zero)
etc

Any ideas? Thanks, Jim.

--

Dave Peterson