Using excel to find most productive day
 

I have a set of numbers with the date next them. There are typically more
then one piece of data per date.
ex.

1/2/08 100
1/2/08 25
1/2/08 87
1/3/08 150
1/4/08 30
1/4/08 40
1/4/08 50
1/4/08 60

*now here I now the most productive (greatest sum) is 1/2/08. But how do I
plug this into Excel.

Thanks

Using excel to find most productive day
 

"But how do I plug this into Excel."

What version of Excel are you using?
In what program/application is the data located?
Have you tried copy and paste?
Once the data is in Excel, have you tried "Subtotals" from the Data menu?
--
Jim Cone
Portland, Oregon USA


"Rower"

wrote in message
I have a set of numbers with the date next them. There are typically more
then one piece of data per date.
ex.
1/2/08 100
1/2/08 25
1/2/08 87
1/3/08 150
1/4/08 30
1/4/08 40
1/4/08 50
1/4/08 60
*now here I now the most productive (greatest sum) is 1/2/08. But how do I
plug this into Excel.
Thanks

Using excel to find most productive day
 

On Sat, 6 Dec 2008 19:42:01 -0800, Rower
wrote:

I have a set of numbers with the date next them. There are typically more
then one piece of data per date.
ex.

1/2/08 100
1/2/08 25
1/2/08 87
1/3/08 150
1/4/08 30
1/4/08 40
1/4/08 50
1/4/08 60

*now here I now the most productive (greatest sum) is 1/2/08. But how do I
plug this into Excel.

Thanks

There are several ways.

Here is a formula.

The formula assumes that your column of dates is NAME'd "Dates"; and that your
column of numbers is NAME'd "Production". But you can use cell references if
you prefer.

This formula must be entered as an **array** formula. That means you hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula:

=INDEX(ROW(INDIRECT(MIN(Dates)&":"&MAX(Dates))),
MATCH(MAX(SUMIF(Dates,ROW(INDIRECT(MIN(Dates)
&":"&MAX(Dates))),Production)),SUMIF(Dates,ROW(
INDIRECT(MIN(Dates)&":"&MAX(Dates))),Production),0 ))
--ron

Using excel to find most productive day
 

Ron - This formula is Outstanding -- For the FIRST Time EVER I used the menu
option Tools, Formula Auditing, Evalute Formula AND WOW!!! What a feature!!
One Question -- I would like to capture and Print out EACH of the Steps Into
a Print-Out so that I can Move away from the Computer and begin the 2-hour
process of studing each step. Can this be done using a VAB routine? I tries
highlighting each single step and Copying (Ctr+C) to paste into a sheet, but
such is not possible.

Can you assist me in nailing this thing down?
Much appreciated,
Jim May

"Ron Rosenfeld" wrote:

On Sat, 6 Dec 2008 19:42:01 -0800, Rower
wrote:

I have a set of numbers with the date next them. There are typically more
then one piece of data per date.
ex.

1/2/08 100
1/2/08 25
1/2/08 87
1/3/08 150
1/4/08 30
1/4/08 40
1/4/08 50
1/4/08 60

*now here I now the most productive (greatest sum) is 1/2/08. But how do I
plug this into Excel.

Thanks

There are several ways.

Here is a formula.

The formula assumes that your column of dates is NAME'd "Dates"; and that your
column of numbers is NAME'd "Production". But you can use cell references if
you prefer.

This formula must be entered as an **array** formula. That means you hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula:

=INDEX(ROW(INDIRECT(MIN(Dates)&":"&MAX(Dates))),
MATCH(MAX(SUMIF(Dates,ROW(INDIRECT(MIN(Dates)
&":"&MAX(Dates))),Production)),SUMIF(Dates,ROW(
INDIRECT(MIN(Dates)&":"&MAX(Dates))),Production),0 ))
--ron

Using excel to find most productive day
 

On Sun, 7 Dec 2008 07:32:00 -0800, JMay wrote:

Ron - This formula is Outstanding -- For the FIRST Time EVER I used the menu
option Tools, Formula Auditing, Evalute Formula AND WOW!!! What a feature!!
One Question -- I would like to capture and Print out EACH of the Steps Into
a Print-Out so that I can Move away from the Computer and begin the 2-hour
process of studing each step. Can this be done using a VAB routine? I tries
highlighting each single step and Copying (Ctr+C) to paste into a sheet, but
such is not possible.

Can you assist me in nailing this thing down?
Much appreciated,
Jim May

"Ron Rosenfeld" wrote:

On Sat, 6 Dec 2008 19:42:01 -0800, Rower
wrote:

I have a set of numbers with the date next them. There are typically more
then one piece of data per date.
ex.

1/2/08 100
1/2/08 25
1/2/08 87
1/3/08 150
1/4/08 30
1/4/08 40
1/4/08 50
1/4/08 60

*now here I now the most productive (greatest sum) is 1/2/08. But how do I
plug this into Excel.

Thanks

There are several ways.

Here is a formula.

The formula assumes that your column of dates is NAME'd "Dates"; and that your
column of numbers is NAME'd "Production". But you can use cell references if
you prefer.

This formula must be entered as an **array** formula. That means you hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula:

=INDEX(ROW(INDIRECT(MIN(Dates)&":"&MAX(Dates))),
MATCH(MAX(SUMIF(Dates,ROW(INDIRECT(MIN(Dates)
&":"&MAX(Dates))),Production)),SUMIF(Dates,ROW(
INDIRECT(MIN(Dates)&":"&MAX(Dates))),Production),0 ))
--ron


I don't believe the Formula Evaluation wizard is accessible from VBA.

But here is a bit of what is going on:

ROW(INDIRECT(MIN(A1:A8)&":"&MAX(A1:A8)))

{39449;39450;39451}

creates an ordered array which includes all of the dates between the earliest
date and the latest date in the data. The dates are returned as serial numbers
(with Jan 1, 1900 = 1)

So the function: SUMIF(dates,{array of dates},production) will return an array
of the sums for each of the dates.

{212;150;180}

Applying the MAX function to that result returns the highest value.

212

Then MATCH tells us where in the array this occurred

1

and we use the INDEX function (against our generated array of dates) to tell
which date it is.
--ron

Using excel to find most productive day
 

Ron Thanks for this BREAK-DOWN. I will PRINT and POUR-OVER your
explanation UNTIL I GET IT !!

Thank you So Very Much !!

This will greatly advance my excel understanding level.

Jim May

"Ron Rosenfeld" wrote:

On Sun, 7 Dec 2008 07:32:00 -0800, JMay wrote:

Ron - This formula is Outstanding -- For the FIRST Time EVER I used the menu
option Tools, Formula Auditing, Evalute Formula AND WOW!!! What a feature!!
One Question -- I would like to capture and Print out EACH of the Steps Into
a Print-Out so that I can Move away from the Computer and begin the 2-hour
process of studing each step. Can this be done using a VAB routine? I tries
highlighting each single step and Copying (Ctr+C) to paste into a sheet, but
such is not possible.

Can you assist me in nailing this thing down?
Much appreciated,
Jim May

"Ron Rosenfeld" wrote:

On Sat, 6 Dec 2008 19:42:01 -0800, Rower
wrote:

I have a set of numbers with the date next them. There are typically more
then one piece of data per date.
ex.

1/2/08 100
1/2/08 25
1/2/08 87
1/3/08 150
1/4/08 30
1/4/08 40
1/4/08 50
1/4/08 60

*now here I now the most productive (greatest sum) is 1/2/08. But how do I
plug this into Excel.

Thanks

There are several ways.

Here is a formula.

The formula assumes that your column of dates is NAME'd "Dates"; and that your
column of numbers is NAME'd "Production". But you can use cell references if
you prefer.

This formula must be entered as an **array** formula. That means you hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula:

=INDEX(ROW(INDIRECT(MIN(Dates)&":"&MAX(Dates))),
MATCH(MAX(SUMIF(Dates,ROW(INDIRECT(MIN(Dates)
&":"&MAX(Dates))),Production)),SUMIF(Dates,ROW(
INDIRECT(MIN(Dates)&":"&MAX(Dates))),Production),0 ))
--ron


I don't believe the Formula Evaluation wizard is accessible from VBA.

But here is a bit of what is going on:

ROW(INDIRECT(MIN(A1:A8)&":"&MAX(A1:A8)))

{39449;39450;39451}

creates an ordered array which includes all of the dates between the earliest
date and the latest date in the data. The dates are returned as serial numbers
(with Jan 1, 1900 = 1)

So the function: SUMIF(dates,{array of dates},production) will return an array
of the sums for each of the dates.

{212;150;180}

Applying the MAX function to that result returns the highest value.

212

Then MATCH tells us where in the array this occurred

1

and we use the INDEX function (against our generated array of dates) to tell
which date it is.
--ron