I'm trying to create statistics from a sheet with order data from my webshop.
Each row consists of an order with:

Column A = order number
Column B = Payment option
Column C = Order date

Example:

1 PayPal 010110
2 VISA 010110
3 PayPal 020110
4 PayPal 020110
5 PayPal 020110
6 AMEX 030110


I'm interested to count how many orders have been placed each day during a
month and display an average based on the dates in column C. That is, 1st of
January = 2, 2nd of January = 3 etc. So for the three days above the average
number of orders/day = (2+3+1)/3 = 2.

I'm also interested to see the maximum number of orders for one day during a
month. That is, for the three day example above the result will be 3.

Best

Hi

With a list of dates in E1:E3, use a helper column (column F in this
example) to count number of orders each day using:

=COUNTIF($C$1:C6;E1)

Calculate average using this - insert the formula in E1 and copy it
down as required:

=AVERAGE($F$1:F1)

To calculate max number of orders in one day use this:

=MAX(F1:F3)

Hopes this helps.
.....
Per



On 7 Jan., 00:43, Rocketeer <Rocket...@discussions.microsoft.com>
wrote:
> I'm trying to create statistics from a sheet with order data from my webshop.
> Each row consists of an order with:
>
> Column A = order number
> Column B = Payment option
> Column C = Order date
>
> Example:
>
> 1 * PayPal * 010110
> 2 * VISA * * 010110
> 3 * PayPal * 020110
> 4 * PayPal * 020110
> 5 * PayPal * 020110
> 6 * AMEX * *030110
>
> I'm interested to count how many orders have been placed each day during a
> month and display an average based on the dates in column C. That is, 1stof
> January = 2, 2nd of January = 3 etc. So for the three days above the average
> number of orders/day = (2+3+1)/3 = 2.
>
> I'm also interested to see the maximum number of orders for one day during a
> month. That is, for the three day example above the result will be 3.
>
> Best

Thanks Per!

I have my list of dates in column C, (C:C). I had a circular reference with
your example. If I use the following formula:

=COUNTIF(C:C;C1)

for each row and my helper column D in this case the example will be:

A B C D
---------------------------
1 PayPal 010110 2
2 VISA 010110 2
3 PayPal 020110 3
4 PayPal 020110 3
5 PayPal 020110 3
6 AMEX 030110 1

But I would prefere this instead:

A B C D
---------------------------
1 PayPal 010110
2 VISA 010110 2
3 PayPal 020110
4 PayPal 020110
5 PayPal 020110 3
6 AMEX 030110 1

or

A B C D
---------------------------
1 PayPal 010110 2
2 VISA 010110
3 PayPal 020110 3
4 PayPal 020110
5 PayPal 020110
6 AMEX 030110 1

Otherwise the average calculation is wrong.

That is, I only want to display the total number of orders for each day once
at the last/first order row in each date group.

Any help appriciated

Best



"Per Jessen" wrote:

> Hi
>
> With a list of dates in E1:E3, use a helper column (column F in this
> example) to count number of orders each day using:
>
> =COUNTIF($C$1:C6;E1)
>
> Calculate average using this - insert the formula in E1 and copy it
> down as required:
>
> =AVERAGE($F$1:F1)
>
> To calculate max number of orders in one day use this:
>
> =MAX(F1:F3)
>
> Hopes this helps.
> .....
> Per
>
>
>
> On 7 Jan., 00:43, Rocketeer <Rocket...@discussions.microsoft.com>
> wrote:
> > I'm trying to create statistics from a sheet with order data from my webshop.
> > Each row consists of an order with:
> >
> > Column A = order number
> > Column B = Payment option
> > Column C = Order date
> >
> > Example:
> >
> > 1 PayPal 010110
> > 2 VISA 010110
> > 3 PayPal 020110
> > 4 PayPal 020110
> > 5 PayPal 020110
> > 6 AMEX 030110
> >
> > I'm interested to count how many orders have been placed each day during a
> > month and display an average based on the dates in column C. That is, 1st of
> > January = 2, 2nd of January = 3 etc. So for the three days above the average
> > number of orders/day = (2+3+1)/3 = 2.
> >
> > I'm also interested to see the maximum number of orders for one day during a
> > month. That is, for the three day example above the result will be 3.
> >
> > Best
>
> .
>

Hi!

It works with the following formula in column D beginning on row 2:

=IF(COUNTIF(C:C;C2)=COUNTIF(C:C;C1);"";COUNTIF(C:C;C2))

Then the example will be:

A B C D
---------------------------
1 PayPal 010110 2
2 VISA 010110
3 PayPal 020110 3
4 PayPal 020110
5 PayPal 020110
6 AMEX 030110 1

Thanks for the inspiration



"Rocketeer" wrote:

> Thanks Per!
>
> I have my list of dates in column C, (C:C). I had a circular reference with
> your example. If I use the following formula:
>
> =COUNTIF(C:C;C1)
>
> for each row and my helper column D in this case the example will be:
>
> A B C D
> ---------------------------
> 1 PayPal 010110 2
> 2 VISA 010110 2
> 3 PayPal 020110 3
> 4 PayPal 020110 3
> 5 PayPal 020110 3
> 6 AMEX 030110 1
>
> But I would prefere this instead:
>
> A B C D
> ---------------------------
> 1 PayPal 010110
> 2 VISA 010110 2
> 3 PayPal 020110
> 4 PayPal 020110
> 5 PayPal 020110 3
> 6 AMEX 030110 1
>
> or
>
> A B C D
> ---------------------------
> 1 PayPal 010110 2
> 2 VISA 010110
> 3 PayPal 020110 3
> 4 PayPal 020110
> 5 PayPal 020110
> 6 AMEX 030110 1
>
> Otherwise the average calculation is wrong.
>
> That is, I only want to display the total number of orders for each day once
> at the last/first order row in each date group.
>
> Any help appriciated
>
> Best
>
>
>
> "Per Jessen" wrote:
>
> > Hi
> >
> > With a list of dates in E1:E3, use a helper column (column F in this
> > example) to count number of orders each day using:
> >
> > =COUNTIF($C$1:C6;E1)
> >
> > Calculate average using this - insert the formula in E1 and copy it
> > down as required:
> >
> > =AVERAGE($F$1:F1)
> >
> > To calculate max number of orders in one day use this:
> >
> > =MAX(F1:F3)
> >
> > Hopes this helps.
> > .....
> > Per
> >
> >
> >
> > On 7 Jan., 00:43, Rocketeer <Rocket...@discussions.microsoft.com>
> > wrote:
> > > I'm trying to create statistics from a sheet with order data from my webshop.
> > > Each row consists of an order with:
> > >
> > > Column A = order number
> > > Column B = Payment option
> > > Column C = Order date
> > >
> > > Example:
> > >
> > > 1 PayPal 010110
> > > 2 VISA 010110
> > > 3 PayPal 020110
> > > 4 PayPal 020110
> > > 5 PayPal 020110
> > > 6 AMEX 030110
> > >
> > > I'm interested to count how many orders have been placed each day during a
> > > month and display an average based on the dates in column C. That is, 1st of
> > > January = 2, 2nd of January = 3 etc. So for the three days above the average
> > > number of orders/day = (2+3+1)/3 = 2.
> > >
> > > I'm also interested to see the maximum number of orders for one day during a
> > > month. That is, for the three day example above the result will be 3.
> > >
> > > Best
> >
> > .
> >