| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max Value - based on a time period
Hi
I have a number of data vaules (Col-B) per day for a number of years. Is there a formula that I could use to extract the MAX value of Col-B for say, Year 2000, 2001, 2002 etc. I have a number of series of data, and each series has approx 10,000 lines of data hence be easier if use formula. The frequency of the data values, varies, there could be 10 on one day, 5 the next etc. I have previously used SUMPRODUCT to get the sumation of similar data but how do I get the MAX value. Any ideas? The Data is as follows. Col-A Col-B 01/01/2000 09:10:18 0.005 01/01/2000 14:22:08 0.008 01/01/2000 23:14:28 0.004 02/01/2000 03:08:36 0.011 02/01/2000 11:10:40 0.018 31/12/2008 09:10:18 0.009 Many Thanks Chris |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Max Value - based on a time period
Try the below. Please note that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =MAX(IF(TEXT(A2:A100,"yyyy")="2009",B2:B100)) If this post helps click Yes --------------- Jacob Skaria "Chris26" wrote: Hi I have a number of data vaules (Col-B) per day for a number of years. Is there a formula that I could use to extract the MAX value of Col-B for say, Year 2000, 2001, 2002 etc. I have a number of series of data, and each series has approx 10,000 lines of data hence be easier if use formula. The frequency of the data values, varies, there could be 10 on one day, 5 the next etc. I have previously used SUMPRODUCT to get the sumation of similar data but how do I get the MAX value. Any ideas? The Data is as follows. Col-A Col-B 01/01/2000 09:10:18 0.005 01/01/2000 14:22:08 0.008 01/01/2000 23:14:28 0.004 02/01/2000 03:08:36 0.011 02/01/2000 11:10:40 0.018 31/12/2008 09:10:18 0.009 Many Thanks Chris |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Max Value - based on a time period
This worked fine, many thanks
Chris "Jacob Skaria" wrote: Try the below. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =MAX(IF(TEXT(A2:A100,"yyyy")="2009",B2:B100)) If this post helps click Yes --------------- Jacob Skaria "Chris26" wrote: Hi I have a number of data vaules (Col-B) per day for a number of years. Is there a formula that I could use to extract the MAX value of Col-B for say, Year 2000, 2001, 2002 etc. I have a number of series of data, and each series has approx 10,000 lines of data hence be easier if use formula. The frequency of the data values, varies, there could be 10 on one day, 5 the next etc. I have previously used SUMPRODUCT to get the sumation of similar data but how do I get the MAX value. Any ideas? The Data is as follows. Col-A Col-B 01/01/2000 09:10:18 0.005 01/01/2000 14:22:08 0.008 01/01/2000 23:14:28 0.004 02/01/2000 03:08:36 0.011 02/01/2000 11:10:40 0.018 31/12/2008 09:10:18 0.009 Many Thanks Chris |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Max Value - based on a time period
"Chris26" wrote:
I have a number of data vaules (Col-B) per day for a number of years. Is there a formula that I could use to extract the MAX value of Col-B for say, Year 2000, 2001, 2002 etc. If you simply want the max of column B for a specified year in column A, try the following array formula (commit with ctrl-shift-Enter, not just Enter): =max(if(year($A$1:$A$10000)=C1,$B$1:$B$10000)) where C1 contains the year. I have purposely mixed absolute and relative references so that you can copy the formula down a column parallel to, say, C1:C9 that contains the years 2000 through 2009. However.... The frequency of the data values, varies, there could be 10 on one day, 5 the next etc. I do not understand the relevancy of that comment. If you are saying that you want the sum of all instances of the max value in a specified year, try the following array formula (again, commit with ctrl-shift-Enter, not just Enter): =sumproduct((year($A$1:$A$10000)=C1)*($B$1:$B$1000 0=max(if(year($A$1:$A$10000)=C1,$B$1:$B$10000))),$ B$1:$B$10000) ----- original message ----- "Chris26" wrote in message ... Hi I have a number of data vaules (Col-B) per day for a number of years. Is there a formula that I could use to extract the MAX value of Col-B for say, Year 2000, 2001, 2002 etc. I have a number of series of data, and each series has approx 10,000 lines of data hence be easier if use formula. The frequency of the data values, varies, there could be 10 on one day, 5 the next etc. I have previously used SUMPRODUCT to get the sumation of similar data but how do I get the MAX value. Any ideas? The Data is as follows. Col-A Col-B 01/01/2000 09:10:18 0.005 01/01/2000 14:22:08 0.008 01/01/2000 23:14:28 0.004 02/01/2000 03:08:36 0.011 02/01/2000 11:10:40 0.018 31/12/2008 09:10:18 0.009 Many Thanks Chris |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Spreading an amount over a period of time based on predetermined % | Excel Worksheet Functions | |||
| I have a problem with conditional formulas based on a time period | Excel Worksheet Functions | |||
| conditional formulas based on a time period | Excel Worksheet Functions | |||
| average rate of change per given time period between 2 moments in time of a value | Excel Worksheet Functions | |||
| Sum column based on value in each row, if two cells equal, or if date is within time period | Excel Worksheet Functions | |||
Hybrid Mode
