If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 ctrlshift 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 |
#4
|
|||
|
|||
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 ctrlshift 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 |
#5
|
|||
|
|||
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 ctrlshift 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 |
#6
|
|||
|
|||
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 ctrlshift 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 |
Thread Tools | |
Display Modes | |
|
|