| 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 an a
I just typed them manually, thanks for helping
"T. Valko" wrote: 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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the Max, Min values based on certain criteria for anarray
|
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the Max, Min values based on certain criteria for an a
Ok, let's try this...
Let's reference the *original time values*. If your original time values are like: 10:01:23... A2:A50 = Times B2:B50 = Values D2 = 10:00 AM D3 = 10:01 AM For the max (array entered**): =MAX(IF((HOUR(Times)=HOUR(D2))*(MINUTE(Times)=MINU TE(D2)),Values)) For the min (array entered**): =MIN(IF((HOUR(Times)=HOUR(D2))*(MINUTE(Times)=MINU TE(D2)),Values)) For the first: =LOOKUP(2,1/((HOUR(Times)=HOUR(D2))*(MINUTE(Times)=MINUTE(D2)) ),Values) For the last (array entered**): =INDEX(Values,MATCH(1,(HOUR(Times)=HOUR(D2))*(MINU TE(Times)=MINUTE(D2)),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 ... I just typed them manually, thanks for helping "T. Valko" wrote: 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. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the Max, Min values based on certain criteria for an a
Hi again,
Thanks for that, that part is working now, just have a few more questions. There is also a volume for a certain time, would I just use a Sumif formula to find the volume at the time? Also, if I want to group the numbers in 5 minute intevals. i.e. 10:00-10:05 how would I go about that? Lastly, How do I plot it on a chart and get it to ignore that 0's that may appear in the data. Thank you so much for the help. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the Max, Min values based on certain criteria for an a
There is also a volume for a certain time, would I
just use a Sumif formula to find the volume at the time? No, you'd need something like this: For the total volume at 10:00 AM... =SUMPRODUCT(--(HOUR(Times)=HOUR(D2)),--(MINUTE(Times)=MINUTE(D2)),Volume)) if I want to group the numbers in 5 minute intevals. i.e. 10:00-10:05 how would I go about that? Do you mean find the max from 10:00 to 10:05 ? Since the time values included minutes and seconds you probably need to adjust the interval to 10:00 AM to 10:06 AM. The reason is that there might be a time of something like 10:05:59 Like this (array entered): D2 = 10:00 AM E2 = 10:06 AM =MAX(IF((Times=D2)*(Times<=E2),Values)) How do I plot it on a chart and get it to ignore that 0's that may appear in the data. I rarely use charts but I think you need to replace 0 values with an NA(). -- Biff Microsoft Excel MVP "swalk88" wrote in message ... Hi again, Thanks for that, that part is working now, just have a few more questions. There is also a volume for a certain time, would I just use a Sumif formula to find the volume at the time? Also, if I want to group the numbers in 5 minute intevals. i.e. 10:00-10:05 how would I go about that? Lastly, How do I plot it on a chart and get it to ignore that 0's that may appear in the data. Thank you so much for the help. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the Max, Min values based on certain criteria for an a
Ooops!
=MAX(IF((Times=D2)*(Times<=E2),Values)) That should be: =MAX(IF((Times=D2)*(Times<E2),Values)) -- Biff Microsoft Excel MVP -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... There is also a volume for a certain time, would I just use a Sumif formula to find the volume at the time? No, you'd need something like this: For the total volume at 10:00 AM... =SUMPRODUCT(--(HOUR(Times)=HOUR(D2)),--(MINUTE(Times)=MINUTE(D2)),Volume)) if I want to group the numbers in 5 minute intevals. i.e. 10:00-10:05 how would I go about that? Do you mean find the max from 10:00 to 10:05 ? Since the time values included minutes and seconds you probably need to adjust the interval to 10:00 AM to 10:06 AM. The reason is that there might be a time of something like 10:05:59 Like this (array entered): D2 = 10:00 AM E2 = 10:06 AM =MAX(IF((Times=D2)*(Times<=E2),Values)) How do I plot it on a chart and get it to ignore that 0's that may appear in the data. I rarely use charts but I think you need to replace 0 values with an NA(). -- Biff Microsoft Excel MVP "swalk88" wrote in message ... Hi again, Thanks for that, that part is working now, just have a few more questions. There is also a volume for a certain time, would I just use a Sumif formula to find the volume at the time? Also, if I want to group the numbers in 5 minute intevals. i.e. 10:00-10:05 how would I go about that? Lastly, How do I plot it on a chart and get it to ignore that 0's that may appear in the data. Thank you so much for the help. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the Max, Min values based on certain criteria for an a
Hi,
One last thing, its all good for most of it now but how would I find the first and last for the five minute intervals, also the total volume. I've worked all the rest out and I have tried inserting the if function into the brackets but that didn't work. Thanks "T. Valko" wrote: Ooops! =MAX(IF((Times=D2)*(Times<=E2),Values)) That should be: =MAX(IF((Times=D2)*(Times<E2),Values)) -- Biff Microsoft Excel MVP -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... There is also a volume for a certain time, would I just use a Sumif formula to find the volume at the time? No, you'd need something like this: For the total volume at 10:00 AM... =SUMPRODUCT(--(HOUR(Times)=HOUR(D2)),--(MINUTE(Times)=MINUTE(D2)),Volume)) if I want to group the numbers in 5 minute intevals. i.e. 10:00-10:05 how would I go about that? Do you mean find the max from 10:00 to 10:05 ? Since the time values included minutes and seconds you probably need to adjust the interval to 10:00 AM to 10:06 AM. The reason is that there might be a time of something like 10:05:59 Like this (array entered): D2 = 10:00 AM E2 = 10:06 AM =MAX(IF((Times=D2)*(Times<=E2),Values)) How do I plot it on a chart and get it to ignore that 0's that may appear in the data. I rarely use charts but I think you need to replace 0 values with an NA(). -- Biff Microsoft Excel MVP "swalk88" wrote in message ... Hi again, Thanks for that, that part is working now, just have a few more questions. There is also a volume for a certain time, would I just use a Sumif formula to find the volume at the time? Also, if I want to group the numbers in 5 minute intevals. i.e. 10:00-10:05 how would I go about that? Lastly, How do I plot it on a chart and get it to ignore that 0's that may appear in the data. Thank you so much for the help. |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the Max, Min values based on certain criteria for an a
One last thing, its all good for most of it now but
how would I find the first and last for the five minute intervals, also the total volume. Since it's a multiple time interval the first and last would be: First would use the same formula as if you were finding the first for 10:00. Last would use the same formula as if you were finding the last for 10:05. For the 5 min volume: D2 = 10:00 AM E2 = 10:05 AM =SUMPRODUCT(--(HOUR(Times)=HOUR(D2)),--(MINUTE(Times)<=MINUTE(D2)),Volume)) Those seconds are really making this more complicated than need be. I don't know why you had trouble trying to convert to just h:mm. -- Biff Microsoft Excel MVP "swalk88" wrote in message ... Hi, One last thing, its all good for most of it now but how would I find the first and last for the five minute intervals, also the total volume. I've worked all the rest out and I have tried inserting the if function into the brackets but that didn't work. Thanks "T. Valko" wrote: Ooops! =MAX(IF((Times=D2)*(Times<=E2),Values)) That should be: =MAX(IF((Times=D2)*(Times<E2),Values)) -- Biff Microsoft Excel MVP -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... There is also a volume for a certain time, would I just use a Sumif formula to find the volume at the time? No, you'd need something like this: For the total volume at 10:00 AM... =SUMPRODUCT(--(HOUR(Times)=HOUR(D2)),--(MINUTE(Times)=MINUTE(D2)),Volume)) if I want to group the numbers in 5 minute intevals. i.e. 10:00-10:05 how would I go about that? Do you mean find the max from 10:00 to 10:05 ? Since the time values included minutes and seconds you probably need to adjust the interval to 10:00 AM to 10:06 AM. The reason is that there might be a time of something like 10:05:59 Like this (array entered): D2 = 10:00 AM E2 = 10:06 AM =MAX(IF((Times=D2)*(Times<=E2),Values)) How do I plot it on a chart and get it to ignore that 0's that may appear in the data. I rarely use charts but I think you need to replace 0 values with an NA(). -- Biff Microsoft Excel MVP "swalk88" wrote in message ... Hi again, Thanks for that, that part is working now, just have a few more questions. There is also a volume for a certain time, would I just use a Sumif formula to find the volume at the time? Also, if I want to group the numbers in 5 minute intevals. i.e. 10:00-10:05 how would I go about that? Lastly, How do I plot it on a chart and get it to ignore that 0's that may appear in the data. Thank you so much for the help. |
| 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) | |||
Linear Mode
