Using excel to find most productive day

  • Thread starter Thread starter Rower
  • Start date Start date
R

Rower

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
 
Re: "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"
<[email protected]>
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
 
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
Click to expand...

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
 
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 - 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
Click to expand...

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
 
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
 
Back
Top