Average starting with first month

[Jim]

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.

[Jezebel]

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!B2>0,Sheet1!B2,IF(Sheet1!A2<>"",0,""))




"Jim" <[email protected]> wrote in message
news:[email protected]...
>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.

[Peo Sjoblom]

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" <[email protected]> wrote in message
news:[email protected]...
>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]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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

[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

[Peo Sjoblom]

Aha

--
Regards,

Peo Sjoblom

(No private emails please)


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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

[Peo Sjoblom]

That is pretty enough

--
Regards,

Peo Sjoblom

(No private emails please)


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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

[Jim]

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
>