| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the Max, Min values based on certain criteria for an array
10:06 AM 9.56
10:06 AM 9.56 10:06 AM 9.56 10:06 AM 9.55 10:05 AM 9.55 10:04 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:02 AM 9.53 10:02 AM 9.53 10:02 AM 9.53 10:01 AM 9.53 10:01 AM 9.53 10:01 AM 9.52 10:01 AM 9.52 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.5 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.51 10:00 AM 9.51 I would like to know how to get the Max Value of say 10:00 AM, Max value of 10:01 AM etc. I would also like a formula to give the Min value and also the start value and end value. There is a seconds component so for 10:00 AM the first value is 9.51 and the last value is 9.51 too, for 10:01 AM the first value is 9.52 and the last is 9.53 etc. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the Max, Min values based on certain criteria for an array
Try these...
E1:H1 = column headers = Max, Min, First, Last D1 = 10:00Am D2 = 10:01 AM Array entered** in E2 and copied down to E3: =MAX(IF(A$2:A$50=D2,B$2:B$50)) Array entered** in F2 and copied down to F3: =MIN(IF(A$2:A$50=D2,B$2:B$50)) Entered in G2 and copied down to G3: =LOOKUP(2,1/(A$2:A$50=D2),B$2:B$50) Entered in H2 and copied down to H3: =VLOOKUP(D2,A$2:B$50,2,0) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "swalk88" wrote in message ... 10:06 AM 9.56 10:06 AM 9.56 10:06 AM 9.56 10:06 AM 9.55 10:05 AM 9.55 10:04 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:02 AM 9.53 10:02 AM 9.53 10:02 AM 9.53 10:01 AM 9.53 10:01 AM 9.53 10:01 AM 9.52 10:01 AM 9.52 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.5 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.51 10:00 AM 9.51 I would like to know how to get the Max Value of say 10:00 AM, Max value of 10:01 AM etc. I would also like a formula to give the Min value and also the start value and end value. There is a seconds component so for 10:00 AM the first value is 9.51 and the last value is 9.51 too, for 10:01 AM the first value is 9.52 and the last is 9.53 etc. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the Max, Min values based on certain criteria for an a
Thanks for that, for some strange reason when I copy it down it eventually
goes to 0 and #NA. I think the problem may lie with the times. Originally the times were in 10:01:23 etc but I rounded it to the nearest minute with this formula =FLOOR(A2,1/1440) and it worked but only for a few "T. Valko" wrote: Try these... E1:H1 = column headers = Max, Min, First, Last D1 = 10:00Am D2 = 10:01 AM Array entered** in E2 and copied down to E3: =MAX(IF(A$2:A$50=D2,B$2:B$50)) Array entered** in F2 and copied down to F3: =MIN(IF(A$2:A$50=D2,B$2:B$50)) Entered in G2 and copied down to G3: =LOOKUP(2,1/(A$2:A$50=D2),B$2:B$50) Entered in H2 and copied down to H3: =VLOOKUP(D2,A$2:B$50,2,0) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "swalk88" wrote in message ... 10:06 AM 9.56 10:06 AM 9.56 10:06 AM 9.56 10:06 AM 9.55 10:05 AM 9.55 10:04 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:02 AM 9.53 10:02 AM 9.53 10:02 AM 9.53 10:01 AM 9.53 10:01 AM 9.53 10:01 AM 9.52 10:01 AM 9.52 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.5 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.51 10:00 AM 9.51 I would like to know how to get the Max Value of say 10:00 AM, Max value of 10:01 AM etc. I would also like a formula to give the Min value and also the start value and end value. There is a seconds component so for 10:00 AM the first value is 9.51 and the last value is 9.51 too, for 10:01 AM the first value is 9.52 and the last is 9.53 etc. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the Max, Min values based on certain criteria for an a
=FLOOR(A2,1/1440)
Try this: =TIME(HOUR(A2),MINUTE(A2),0) -- Biff Microsoft Excel MVP "swalk88" wrote in message ... Thanks for that, for some strange reason when I copy it down it eventually goes to 0 and #NA. I think the problem may lie with the times. Originally the times were in 10:01:23 etc but I rounded it to the nearest minute with this formula =FLOOR(A2,1/1440) and it worked but only for a few "T. Valko" wrote: Try these... E1:H1 = column headers = Max, Min, First, Last D1 = 10:00Am D2 = 10:01 AM Array entered** in E2 and copied down to E3: =MAX(IF(A$2:A$50=D2,B$2:B$50)) Array entered** in F2 and copied down to F3: =MIN(IF(A$2:A$50=D2,B$2:B$50)) Entered in G2 and copied down to G3: =LOOKUP(2,1/(A$2:A$50=D2),B$2:B$50) Entered in H2 and copied down to H3: =VLOOKUP(D2,A$2:B$50,2,0) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "swalk88" wrote in message ... 10:06 AM 9.56 10:06 AM 9.56 10:06 AM 9.56 10:06 AM 9.55 10:05 AM 9.55 10:04 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:02 AM 9.53 10:02 AM 9.53 10:02 AM 9.53 10:01 AM 9.53 10:01 AM 9.53 10:01 AM 9.52 10:01 AM 9.52 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.5 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.51 10:00 AM 9.51 I would like to know how to get the Max Value of say 10:00 AM, Max value of 10:01 AM etc. I would also like a formula to give the Min value and also the start value and end value. There is a seconds component so for 10:00 AM the first value is 9.51 and the last value is 9.51 too, for 10:01 AM the first value is 9.52 and the last is 9.53 etc. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the Max, Min values based on certain criteria for an a
Still nothing for the other ones, any other suggestions?
"T. Valko" wrote: =FLOOR(A2,1/1440) Try this: =TIME(HOUR(A2),MINUTE(A2),0) -- Biff Microsoft Excel MVP "swalk88" wrote in message ... Thanks for that, for some strange reason when I copy it down it eventually goes to 0 and #NA. I think the problem may lie with the times. Originally the times were in 10:01:23 etc but I rounded it to the nearest minute with this formula =FLOOR(A2,1/1440) and it worked but only for a few "T. Valko" wrote: Try these... E1:H1 = column headers = Max, Min, First, Last D1 = 10:00Am D2 = 10:01 AM Array entered** in E2 and copied down to E3: =MAX(IF(A$2:A$50=D2,B$2:B$50)) Array entered** in F2 and copied down to F3: =MIN(IF(A$2:A$50=D2,B$2:B$50)) Entered in G2 and copied down to G3: =LOOKUP(2,1/(A$2:A$50=D2),B$2:B$50) Entered in H2 and copied down to H3: =VLOOKUP(D2,A$2:B$50,2,0) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "swalk88" wrote in message ... 10:06 AM 9.56 10:06 AM 9.56 10:06 AM 9.56 10:06 AM 9.55 10:05 AM 9.55 10:04 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:02 AM 9.53 10:02 AM 9.53 10:02 AM 9.53 10:01 AM 9.53 10:01 AM 9.53 10:01 AM 9.52 10:01 AM 9.52 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.5 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.51 10:00 AM 9.51 I would like to know how to get the Max Value of say 10:00 AM, Max value of 10:01 AM etc. I would also like a formula to give the Min value and also the start value and end value. There is a seconds component so for 10:00 AM the first value is 9.51 and the last value is 9.51 too, for 10:01 AM the first value is 9.52 and the last is 9.53 etc. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the Max, Min values based on certain criteria for an a
For your lookup times:
10:00 AM 10:01 AM How did you enter these times? Did you manually type them in or did you do a fill series or use a formula? -- Biff Microsoft Excel MVP "swalk88" wrote in message ... Still nothing for the other ones, any other suggestions? "T. Valko" wrote: =FLOOR(A2,1/1440) Try this: =TIME(HOUR(A2),MINUTE(A2),0) -- Biff Microsoft Excel MVP "swalk88" wrote in message ... Thanks for that, for some strange reason when I copy it down it eventually goes to 0 and #NA. I think the problem may lie with the times. Originally the times were in 10:01:23 etc but I rounded it to the nearest minute with this formula =FLOOR(A2,1/1440) and it worked but only for a few "T. Valko" wrote: Try these... E1:H1 = column headers = Max, Min, First, Last D1 = 10:00Am D2 = 10:01 AM Array entered** in E2 and copied down to E3: =MAX(IF(A$2:A$50=D2,B$2:B$50)) Array entered** in F2 and copied down to F3: =MIN(IF(A$2:A$50=D2,B$2:B$50)) Entered in G2 and copied down to G3: =LOOKUP(2,1/(A$2:A$50=D2),B$2:B$50) Entered in H2 and copied down to H3: =VLOOKUP(D2,A$2:B$50,2,0) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "swalk88" wrote in message ... 10:06 AM 9.56 10:06 AM 9.56 10:06 AM 9.56 10:06 AM 9.55 10:05 AM 9.55 10:04 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:03 AM 9.55 10:02 AM 9.53 10:02 AM 9.53 10:02 AM 9.53 10:01 AM 9.53 10:01 AM 9.53 10:01 AM 9.52 10:01 AM 9.52 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.5 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.51 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.5 10:00 AM 9.51 10:00 AM 9.51 I would like to know how to get the Max Value of say 10:00 AM, Max value of 10:01 AM etc. I would also like a formula to give the Min value and also the start value and end value. There is a seconds component so for 10:00 AM the first value is 9.51 and the last value is 9.51 too, for 10:01 AM the first value is 9.52 and the last is 9.53 etc. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the Max, Min values based on certain criteria for anarray
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding the last entry in a column based on criteria | Excel Worksheet Functions | |||
| Finding a number based on a criteria | Excel Worksheet Functions | |||
| Finding cell contents based on certain criteria | Excel Discussion (Misc queries) | |||
| Finding cell contents based on certain criteria | Excel Discussion (Misc queries) | |||
| Finding the Largest Number, based on two criteria | Excel Discussion (Misc queries) | |||
Hybrid Mode
