Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating with Variable Search
Hello everyone and I hope someone can help me on this. I am working on
a spreadsheet and I am trying to automate some of the calculations in the document I am running into a sticking point and I wanted to see if anyone has an idea on how to get this to work. Issue: I am receiving data from an exported report that works within Excel. It has a date and timestamp plus some recorded data associated with that timestamp. What I have going on right now is I have the following columns in the export. | Device | SubDevice | Date / TIme | Reading_1 | What I have set right now are three columns to the right of the reading. They look at the data in each reading column and apply the following. The first column looks at Reading_1 and sees if it is above a threshold, if so it labels it with a Y (named: #). The second column looks at that same Reading_1 and if the number is below the threshold it tags it with X (named:<#). A third column does an "ifand" which says if the previous # reading is a Y and the current <# is a X to mark third column clear (named: ok). What I am trying to do is take these columns in someway have them calculate the difference between the start and clear times. Basically I am looking to see if there is way to automate a calculation that looks at the ok column and if it shows ok to subtract that value from the last ok reading. So if it happens 20 timestamps back, excel finds it and makes the calculation. Any thoughts on how i can do this? Thanks! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating with Variable Search
If this data is imported from another application is the date/time stamp a
true Excel date/time? If C2 is a date/time stamp what is the result of this formula: =ISNUMBER(C2) Can you post a small example of your data with the expected result? Don't really need the Device/SubDevice data. Just the date/time and the X/Y data -- Biff Microsoft Excel MVP "markd951" wrote in message ... Hello everyone and I hope someone can help me on this. I am working on a spreadsheet and I am trying to automate some of the calculations in the document I am running into a sticking point and I wanted to see if anyone has an idea on how to get this to work. Issue: I am receiving data from an exported report that works within Excel. It has a date and timestamp plus some recorded data associated with that timestamp. What I have going on right now is I have the following columns in the export. | Device | SubDevice | Date / TIme | Reading_1 | What I have set right now are three columns to the right of the reading. They look at the data in each reading column and apply the following. The first column looks at Reading_1 and sees if it is above a threshold, if so it labels it with a Y (named: #). The second column looks at that same Reading_1 and if the number is below the threshold it tags it with X (named:<#). A third column does an "ifand" which says if the previous # reading is a Y and the current <# is a X to mark third column clear (named: ok). What I am trying to do is take these columns in someway have them calculate the difference between the start and clear times. Basically I am looking to see if there is way to automate a calculation that looks at the ok column and if it shows ok to subtract that value from the last ok reading. So if it happens 20 timestamps back, excel finds it and makes the calculation. Any thoughts on how i can do this? Thanks! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating with Variable Search
On Nov 21, 11:04*pm, "T. Valko" wrote:
If this data is imported from another application is the date/time stamp a true Excel date/time? If C2 is a date/time stamp what is the result of this formula: =ISNUMBER(C2) Can you post a small example of your data with the expected result? Don't really need the Device/SubDevice data. Just the date/time and the X/Y data -- Biff Microsoft Excel MVP "markd951" wrote in message ... Hello everyone and I hope someone can help me on this. I am working on a spreadsheet and I am trying to automate some of the calculations in the document I am running into a sticking point and I wanted to see if anyone has an idea on how to get this to work. Issue: I am receiving data from an exported report that works within Excel. It has a date and timestamp plus some recorded data associated with that timestamp. *What I have going on right now is I have the following columns in the export. | Device | SubDevice | Date / TIme | Reading_1 | What I have set right now are three columns to the right of the reading. They look at the data in each reading column and apply the following. The first column looks at Reading_1 and sees if it is above a threshold, if so it labels it with a Y (named: #). The second column looks at that same Reading_1 and if the number is below the threshold it tags it with X (named:<#). A third column does an "ifand" which says if the previous # reading is a Y and the current <# is a X to mark third column clear (named: ok). What I am trying to do is take these columns in someway have them calculate the difference between the start and clear times. Basically I am looking to see if there is way to automate a calculation that looks at the ok column and if it shows ok to subtract that value from the last ok reading. So if it happens 20 timestamps back, excel finds it and makes the calculation. Any thoughts on how i can do this? Thanks!- Hide quoted text - - Show quoted text - Hi Biff, When I ran =ISNUMBER(c2) the result came up as TRUE. Here is a sample of the data I am looking at. In column labeled 1 on the example you can see at 18:13 timestamp the number is below 126 which would clear the condition so anything before it would be what I want calculated. the second column is showing the =ISNUMBER(C2) that you asked about. Thanks for the help so far! Date / Time 1 2 11/3/2009 0:00 TRUE 128.2 128.2 11/3/2009 4:21 126.9 128.2 11/3/2009 4:44 126.9 126.9 11/3/2009 8:27 126.9 128.2 11/3/2009 8:28 126.9 126.7 11/3/2009 8:29 126.9 128 11/3/2009 11:35 126.9 126.7 11/3/2009 11:35 126.9 128 11/3/2009 11:45 128.2 128 11/3/2009 17:15 126.9 128 11/3/2009 17:15 126.9 126.7 11/3/2009 17:59 126.9 125.4 11/3/2009 18:04 126.9 126.7 11/3/2009 18:13 125.6 126.7 11/3/2009 18:29 126.9 126.7 11/3/2009 20:50 128.2 126.7 11/3/2009 20:56 128.2 128 Mark |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating with Variable Search
Ok, let's see if I understand this...
Based on the posted sample data... 11/3/2009 0:00 is the start time 11/3/2009 18:13 is the stop time because 125.6 in column B is the first instance of a value in column B that is less than 126. Find the time difference between "stop" and "start"? Ok, a couple of questions... Are the date/time stamps all the same date? Is there *always* at least one instance where the value in column B falls below a threshold therefore making it a stop time? -- Biff Microsoft Excel MVP "markd951" wrote in message ... On Nov 21, 11:04 pm, "T. Valko" wrote: If this data is imported from another application is the date/time stamp a true Excel date/time? If C2 is a date/time stamp what is the result of this formula: =ISNUMBER(C2) Can you post a small example of your data with the expected result? Don't really need the Device/SubDevice data. Just the date/time and the X/Y data -- Biff Microsoft Excel MVP "markd951" wrote in message ... Hello everyone and I hope someone can help me on this. I am working on a spreadsheet and I am trying to automate some of the calculations in the document I am running into a sticking point and I wanted to see if anyone has an idea on how to get this to work. Issue: I am receiving data from an exported report that works within Excel. It has a date and timestamp plus some recorded data associated with that timestamp. What I have going on right now is I have the following columns in the export. | Device | SubDevice | Date / TIme | Reading_1 | What I have set right now are three columns to the right of the reading. They look at the data in each reading column and apply the following. The first column looks at Reading_1 and sees if it is above a threshold, if so it labels it with a Y (named: #). The second column looks at that same Reading_1 and if the number is below the threshold it tags it with X (named:<#). A third column does an "ifand" which says if the previous # reading is a Y and the current <# is a X to mark third column clear (named: ok). What I am trying to do is take these columns in someway have them calculate the difference between the start and clear times. Basically I am looking to see if there is way to automate a calculation that looks at the ok column and if it shows ok to subtract that value from the last ok reading. So if it happens 20 timestamps back, excel finds it and makes the calculation. Any thoughts on how i can do this? Thanks!- Hide quoted text - - Show quoted text - Hi Biff, When I ran =ISNUMBER(c2) the result came up as TRUE. Here is a sample of the data I am looking at. In column labeled 1 on the example you can see at 18:13 timestamp the number is below 126 which would clear the condition so anything before it would be what I want calculated. the second column is showing the =ISNUMBER(C2) that you asked about. Thanks for the help so far! Date / Time 1 2 11/3/2009 0:00 TRUE 128.2 128.2 11/3/2009 4:21 126.9 128.2 11/3/2009 4:44 126.9 126.9 11/3/2009 8:27 126.9 128.2 11/3/2009 8:28 126.9 126.7 11/3/2009 8:29 126.9 128 11/3/2009 11:35 126.9 126.7 11/3/2009 11:35 126.9 128 11/3/2009 11:45 128.2 128 11/3/2009 17:15 126.9 128 11/3/2009 17:15 126.9 126.7 11/3/2009 17:59 126.9 125.4 11/3/2009 18:04 126.9 126.7 11/3/2009 18:13 125.6 126.7 11/3/2009 18:29 126.9 126.7 11/3/2009 20:50 128.2 126.7 11/3/2009 20:56 128.2 128 Mark |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating with Variable Search
Hey Biff -
The answers to the questions you posed... 11/3/09 0:00 is in fact the start time. 11/3/09 18:13 value is the stop time as you mentioned Yes I want to find the value between the start and stop time. All the date and time stamps will be different dates as the sheet moves along. So it would be days worth of data (11/3 to say 11/7). There will always (I hope) be a value in either column A or B that fails below threshold making a stop time possible. The thing is Column A and Column B will be independent of each other for what I am doing. So if we focus on getting column A to work I should be able to get column B going afterwards. Thanks. On Nov 23, 12:37*pm, "T. Valko" wrote: Ok, let's see if I understand this... Based on the posted sample data... 11/3/2009 0:00 is the start time 11/3/2009 18:13 is the stop time because 125.6 in column B is the first instance of a value in column B that is less than 126. Find the time difference between "stop" and "start"? Ok, a couple of questions... Are the date/time stamps all the same date? Is there *always* at least one instance where the value in column B falls below a threshold therefore making it a stop time? -- Biff Microsoft Excel MVP "markd951" wrote in message ... On Nov 21, 11:04 pm, "T. Valko" wrote: If this data is imported from another application is the date/time stamp a true Excel date/time? If C2 is a date/time stamp what is the result of this formula: =ISNUMBER(C2) Can you post a small example of your data with the expected result? Don't really need the Device/SubDevice data. Just the date/time and the X/Y data -- Biff Microsoft Excel MVP "markd951" wrote in message .... Hello everyone and I hope someone can help me on this. I am working on a spreadsheet and I am trying to automate some of the calculations in the document I am running into a sticking point and I wanted to see if anyone has an idea on how to get this to work. Issue: I am receiving data from an exported report that works within Excel. It has a date and timestamp plus some recorded data associated with that timestamp. What I have going on right now is I have the following columns in the export. | Device | SubDevice | Date / TIme | Reading_1 | What I have set right now are three columns to the right of the reading. They look at the data in each reading column and apply the following. The first column looks at Reading_1 and sees if it is above a threshold, if so it labels it with a Y (named: #). The second column looks at that same Reading_1 and if the number is below the threshold it tags it with X (named:<#). A third column does an "ifand" which says if the previous # reading is a Y and the current <# is a X to mark third column clear (named: ok). What I am trying to do is take these columns in someway have them calculate the difference between the start and clear times. Basically I am looking to see if there is way to automate a calculation that looks at the ok column and if it shows ok to subtract that value from the last ok reading. So if it happens 20 timestamps back, excel finds it and makes the calculation. Any thoughts on how i can do this? Thanks!- Hide quoted text - - Show quoted text - Hi Biff, When I ran =ISNUMBER(c2) the result came up as TRUE. Here is a sample of the data I am looking at. In column labeled 1 on the example you can see at 18:13 timestamp the number is below 126 which would clear the condition so anything before it would be what I want calculated. the second column is showing the =ISNUMBER(C2) that you asked about. Thanks for the help so far! Date / Time * * 1 * * * * * * *2 11/3/2009 0:00 TRUE 128.2 128.2 11/3/2009 4:21 126.9 128.2 11/3/2009 4:44 126.9 126.9 11/3/2009 8:27 126.9 128.2 11/3/2009 8:28 126.9 126.7 11/3/2009 8:29 126.9 128 11/3/2009 11:35 126.9 126.7 11/3/2009 11:35 126.9 128 11/3/2009 11:45 128.2 128 11/3/2009 17:15 126.9 128 11/3/2009 17:15 126.9 126.7 11/3/2009 17:59 126.9 125.4 11/3/2009 18:04 126.9 126.7 11/3/2009 18:13 125.6 126.7 11/3/2009 18:29 126.9 126.7 11/3/2009 20:50 128.2 126.7 11/3/2009 20:56 128.2 128 Mark- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating with Variable Search
Ok, let's start with this array formula** based on the posted sample data.
=IF(B2<126,0,INDEX(A2:A18,MATCH(TRUE,B2:B18<126,0) )-A2) Format as [h]:mm Result = 18:13 ** 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 "markd951" wrote in message ... Hey Biff - The answers to the questions you posed... 11/3/09 0:00 is in fact the start time. 11/3/09 18:13 value is the stop time as you mentioned Yes I want to find the value between the start and stop time. All the date and time stamps will be different dates as the sheet moves along. So it would be days worth of data (11/3 to say 11/7). There will always (I hope) be a value in either column A or B that fails below threshold making a stop time possible. The thing is Column A and Column B will be independent of each other for what I am doing. So if we focus on getting column A to work I should be able to get column B going afterwards. Thanks. On Nov 23, 12:37 pm, "T. Valko" wrote: Ok, let's see if I understand this... Based on the posted sample data... 11/3/2009 0:00 is the start time 11/3/2009 18:13 is the stop time because 125.6 in column B is the first instance of a value in column B that is less than 126. Find the time difference between "stop" and "start"? Ok, a couple of questions... Are the date/time stamps all the same date? Is there *always* at least one instance where the value in column B falls below a threshold therefore making it a stop time? -- Biff Microsoft Excel MVP "markd951" wrote in message ... On Nov 21, 11:04 pm, "T. Valko" wrote: If this data is imported from another application is the date/time stamp a true Excel date/time? If C2 is a date/time stamp what is the result of this formula: =ISNUMBER(C2) Can you post a small example of your data with the expected result? Don't really need the Device/SubDevice data. Just the date/time and the X/Y data -- Biff Microsoft Excel MVP "markd951" wrote in message ... Hello everyone and I hope someone can help me on this. I am working on a spreadsheet and I am trying to automate some of the calculations in the document I am running into a sticking point and I wanted to see if anyone has an idea on how to get this to work. Issue: I am receiving data from an exported report that works within Excel. It has a date and timestamp plus some recorded data associated with that timestamp. What I have going on right now is I have the following columns in the export. | Device | SubDevice | Date / TIme | Reading_1 | What I have set right now are three columns to the right of the reading. They look at the data in each reading column and apply the following. The first column looks at Reading_1 and sees if it is above a threshold, if so it labels it with a Y (named: #). The second column looks at that same Reading_1 and if the number is below the threshold it tags it with X (named:<#). A third column does an "ifand" which says if the previous # reading is a Y and the current <# is a X to mark third column clear (named: ok). What I am trying to do is take these columns in someway have them calculate the difference between the start and clear times. Basically I am looking to see if there is way to automate a calculation that looks at the ok column and if it shows ok to subtract that value from the last ok reading. So if it happens 20 timestamps back, excel finds it and makes the calculation. Any thoughts on how i can do this? Thanks!- Hide quoted text - - Show quoted text - Hi Biff, When I ran =ISNUMBER(c2) the result came up as TRUE. Here is a sample of the data I am looking at. In column labeled 1 on the example you can see at 18:13 timestamp the number is below 126 which would clear the condition so anything before it would be what I want calculated. the second column is showing the =ISNUMBER(C2) that you asked about. Thanks for the help so far! Date / Time 1 2 11/3/2009 0:00 TRUE 128.2 128.2 11/3/2009 4:21 126.9 128.2 11/3/2009 4:44 126.9 126.9 11/3/2009 8:27 126.9 128.2 11/3/2009 8:28 126.9 126.7 11/3/2009 8:29 126.9 128 11/3/2009 11:35 126.9 126.7 11/3/2009 11:35 126.9 128 11/3/2009 11:45 128.2 128 11/3/2009 17:15 126.9 128 11/3/2009 17:15 126.9 126.7 11/3/2009 17:59 126.9 125.4 11/3/2009 18:04 126.9 126.7 11/3/2009 18:13 125.6 126.7 11/3/2009 18:29 126.9 126.7 11/3/2009 20:50 128.2 126.7 11/3/2009 20:56 128.2 128 Mark- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating with Variable Search
Hey Biff-
I tried the formula but it fails. I don't why when I sent the sample data to you it included a true in that stuff. It should have been this DATE / TIME V1 V2 11/3/2009 0:00 128.2 128.2 11/3/2009 4:21 126.9 128.2 11/3/2009 4:44 126.9 126.9 11/3/2009 8:27 126.9 128.2 11/3/2009 8:28 126.9 126.7 11/3/2009 8:29 126.9 128 11/3/2009 11:35 126.9 126.7 11/3/2009 11:35 126.9 128 11/3/2009 11:45 128.2 128 11/3/2009 17:15 126.9 128 11/3/2009 17:15 126.9 126.7 11/3/2009 17:59 126.9 125.4 11/3/2009 18:04 126.9 126.7 11/3/2009 18:13 125.6 126.7 Should the array still work in this case? On Nov 25, 12:58*pm, "T. Valko" wrote: Ok, let's start with this array formula** based on the posted sample data.. =IF(B2<126,0,INDEX(A2:A18,MATCH(TRUE,B2:B18<126,0) )-A2) Format as [h]:mm Result = 18:13 ** 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 "markd951" wrote in message ... Hey Biff - The answers to the questions you posed... 11/3/09 0:00 is in fact the start time. 11/3/09 18:13 value is the stop time as you mentioned Yes I want to find the value between the start and stop time. All the date and time stamps will be different dates as the sheet moves along. So it would be days worth of data (11/3 to say 11/7). There will always (I hope) be a value in either column A or B that fails below threshold making a stop time possible. The thing is Column A and Column B will be independent of each other for what I am doing. So if we focus on getting column A to work I should be able to get column B going afterwards. Thanks. On Nov 23, 12:37 pm, "T. Valko" wrote: Ok, let's see if I understand this... Based on the posted sample data... 11/3/2009 0:00 is the start time 11/3/2009 18:13 is the stop time because 125.6 in column B is the first instance of a value in column B that is less than 126. Find the time difference between "stop" and "start"? Ok, a couple of questions... Are the date/time stamps all the same date? Is there *always* at least one instance where the value in column B falls below a threshold therefore making it a stop time? -- Biff Microsoft Excel MVP "markd951" wrote in message .... On Nov 21, 11:04 pm, "T. Valko" wrote: If this data is imported from another application is the date/time stamp a true Excel date/time? If C2 is a date/time stamp what is the result of this formula: =ISNUMBER(C2) Can you post a small example of your data with the expected result? Don't really need the Device/SubDevice data. Just the date/time and the X/Y data -- Biff Microsoft Excel MVP "markd951" wrote in message .... Hello everyone and I hope someone can help me on this. I am working on a spreadsheet and I am trying to automate some of the calculations in the document I am running into a sticking point and I wanted to see if anyone has an idea on how to get this to work. Issue: I am receiving data from an exported report that works within Excel.. It has a date and timestamp plus some recorded data associated with that timestamp. What I have going on right now is I have the following columns in the export. | Device | SubDevice | Date / TIme | Reading_1 | What I have set right now are three columns to the right of the reading. They look at the data in each reading column and apply the following. The first column looks at Reading_1 and sees if it is above a threshold, if so it labels it with a Y (named: #). The second column looks at that same Reading_1 and if the number is below the threshold it tags it with X (named:<#). A third column does an "ifand" which says if the previous # reading is a Y and the current <# is a X to mark third column clear (named: ok). What I am trying to do is take these columns in someway have them calculate the difference between the start and clear times. Basically I am looking to see if there is way to automate a calculation that looks at the ok column and if it shows ok to subtract that value from the last ok reading. So if it happens 20 timestamps back, excel finds it and makes the calculation. |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating with Variable Search
I have a feeling that there's more to this than you're telling me. That's
why in my last reply I said: let's start with this.... So, I put together a small sample file based on your posted data. xmarkd951.xls 14kb http://cjoint.com/?lBwAdcYGwi -- Biff Microsoft Excel MVP "markd951" wrote in message ... Hey Biff- I tried the formula but it fails. I don't why when I sent the sample data to you it included a true in that stuff. It should have been this DATE / TIME V1 V2 11/3/2009 0:00 128.2 128.2 11/3/2009 4:21 126.9 128.2 11/3/2009 4:44 126.9 126.9 11/3/2009 8:27 126.9 128.2 11/3/2009 8:28 126.9 126.7 11/3/2009 8:29 126.9 128 11/3/2009 11:35 126.9 126.7 11/3/2009 11:35 126.9 128 11/3/2009 11:45 128.2 128 11/3/2009 17:15 126.9 128 11/3/2009 17:15 126.9 126.7 11/3/2009 17:59 126.9 125.4 11/3/2009 18:04 126.9 126.7 11/3/2009 18:13 125.6 126.7 Should the array still work in this case? On Nov 25, 12:58 pm, "T. Valko" wrote: Ok, let's start with this array formula** based on the posted sample data. =IF(B2<126,0,INDEX(A2:A18,MATCH(TRUE,B2:B18<126,0) )-A2) Format as [h]:mm Result = 18:13 ** 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 "markd951" wrote in message ... Hey Biff - The answers to the questions you posed... 11/3/09 0:00 is in fact the start time. 11/3/09 18:13 value is the stop time as you mentioned Yes I want to find the value between the start and stop time. All the date and time stamps will be different dates as the sheet moves along. So it would be days worth of data (11/3 to say 11/7). There will always (I hope) be a value in either column A or B that fails below threshold making a stop time possible. The thing is Column A and Column B will be independent of each other for what I am doing. So if we focus on getting column A to work I should be able to get column B going afterwards. Thanks. On Nov 23, 12:37 pm, "T. Valko" wrote: Ok, let's see if I understand this... Based on the posted sample data... 11/3/2009 0:00 is the start time 11/3/2009 18:13 is the stop time because 125.6 in column B is the first instance of a value in column B that is less than 126. Find the time difference between "stop" and "start"? Ok, a couple of questions... Are the date/time stamps all the same date? Is there *always* at least one instance where the value in column B falls below a threshold therefore making it a stop time? -- Biff Microsoft Excel MVP "markd951" wrote in message ... On Nov 21, 11:04 pm, "T. Valko" wrote: If this data is imported from another application is the date/time stamp a true Excel date/time? If C2 is a date/time stamp what is the result of this formula: =ISNUMBER(C2) Can you post a small example of your data with the expected result? Don't really need the Device/SubDevice data. Just the date/time and the X/Y data -- Biff Microsoft Excel MVP "markd951" wrote in message ... Hello everyone and I hope someone can help me on this. I am working on a spreadsheet and I am trying to automate some of the calculations in the document I am running into a sticking point and I wanted to see if anyone has an idea on how to get this to work. Issue: I am receiving data from an exported report that works within Excel. It has a date and timestamp plus some recorded data associated with that timestamp. What I have going on right now is I have the following columns in the export. | Device | SubDevice | Date / TIme | Reading_1 | What I have set right now are three columns to the right of the reading. They look at the data in each reading column and apply the following. The first column looks at Reading_1 and sees if it is above a threshold, if so it labels it with a Y (named: #). The second column looks at that same Reading_1 and if the number is below the threshold it tags it with X (named:<#). A third column does an "ifand" which says if the previous # reading is a Y and the current <# is a X to mark third column clear (named: ok). What I am trying to do is take these columns in someway have them calculate the difference between the start and clear times. Basically I am looking to see if there is way to automate a calculation that looks at the ok column and if it shows ok to subtract that value from the last ok reading. So if it happens 20 timestamps back, excel finds it and makes the calculation. Any thoughts on how i can do this? Thanks!- Hide quoted text - - Show quoted text - Hi Biff, When I ran =ISNUMBER(c2) the result came up as TRUE. Here is a sample of the data I am looking at. In column labeled 1 on the example you can see at 18:13 timestamp the number is below 126 which would clear the condition so anything before it would be what I want calculated. the second column is showing the =ISNUMBER(C2) that you asked about. Thanks for the help so far! Date / Time 1 2 11/3/2009 0:00 TRUE 128.2 128.2 11/3/2009 4:21 126.9 128.2 11/3/2009 4:44 126.9 126.9 11/3/2009 8:27 126.9 128.2 11/3/2009 8:28 126.9 126.7 11/3/2009 8:29 126.9 128 11/3/2009 11:35 126.9 126.7 11/3/2009 11:35 126.9 128 11/3/2009 11:45 128.2 128 11/3/2009 17:15 126.9 128 11/3/2009 17:15 126.9 126.7 11/3/2009 17:59 126.9 125.4 11/3/2009 18:04 126.9 126.7 11/3/2009 18:13 125.6 126.7 11/3/2009 18:29 126.9 126.7 11/3/2009 20:50 128.2 126.7 11/3/2009 20:56 128.2 128 Mark- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating with Variable Search
Hey Biff -
I follow the logic here, but when I input the formula the return I get is "#VALUE!" when the calculation completes. Any thoughts? On Nov 27, 4:34*pm, "T. Valko" wrote: I have a feeling that there's more to this than you're telling me. That's why in my last reply I said: let's start with this.... So, I put together a small sample file based on your posted data. xmarkd951.xls *14kb http://cjoint.com/?lBwAdcYGwi -- Biff Microsoft Excel MVP "markd951" wrote in message ... Hey Biff- I tried the formula but it fails. I don't why when I sent the sample data to you it included a true in that stuff. It should have been this DATE / TIME * * * *V1 * * * * * V2 11/3/2009 0:00 128.2 128.2 11/3/2009 4:21 126.9 128.2 11/3/2009 4:44 126.9 126.9 11/3/2009 8:27 126.9 128.2 11/3/2009 8:28 126.9 126.7 11/3/2009 8:29 126.9 128 11/3/2009 11:35 126.9 126.7 11/3/2009 11:35 126.9 128 11/3/2009 11:45 128.2 128 11/3/2009 17:15 126.9 128 11/3/2009 17:15 126.9 126.7 11/3/2009 17:59 126.9 125.4 11/3/2009 18:04 126.9 126.7 11/3/2009 18:13 125.6 126.7 Should the array still work in this case? On Nov 25, 12:58 pm, "T. Valko" wrote: Ok, let's start with this array formula** based on the posted sample data. =IF(B2<126,0,INDEX(A2:A18,MATCH(TRUE,B2:B18<126,0) )-A2) Format as [h]:mm Result = 18:13 ** 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 "markd951" wrote in message ... Hey Biff - The answers to the questions you posed... 11/3/09 0:00 is in fact the start time. 11/3/09 18:13 value is the stop time as you mentioned Yes I want to find the value between the start and stop time. All the date and time stamps will be different dates as the sheet moves along. So it would be days worth of data (11/3 to say 11/7). There will always (I hope) be a value in either column A or B that fails below threshold making a stop time possible. The thing is Column A and Column B will be independent of each other for what I am doing. So if we focus on getting column A to work I should be able to get column B going afterwards. Thanks. On Nov 23, 12:37 pm, "T. Valko" wrote: Ok, let's see if I understand this... Based on the posted sample data... 11/3/2009 0:00 is the start time 11/3/2009 18:13 is the stop time because 125.6 in column B is the first instance of a value in column B that is less than 126. Find the time difference between "stop" and "start"? Ok, a couple of questions... Are the date/time stamps all the same date? Is there *always* at least one instance where the value in column B falls below a threshold therefore making it a stop time? -- Biff Microsoft Excel MVP "markd951" wrote in message .... On Nov 21, 11:04 pm, "T. Valko" wrote: If this data is imported from another application is the date/time stamp a true Excel date/time? If C2 is a date/time stamp what is the result of this formula: =ISNUMBER(C2) Can you post a small example of your data with the expected result? Don't really need the Device/SubDevice data. Just the date/time and the X/Y data -- Biff Microsoft Excel MVP "markd951" wrote in message ... Hello everyone and I hope someone can help me on this. I am working on a spreadsheet and I am trying to automate some of the calculations in the document I am running into a sticking point and I wanted to see if anyone has an idea on how to get this to work. Issue: I am receiving data from an exported report that works within Excel. It has a date and timestamp plus some recorded data associated with that timestamp. What I have going on right now is I have the following columns in the export. | Device | SubDevice | Date / TIme | Reading_1 | What I have set right now are three columns to the right of the reading. They look at the data in each reading column and apply the following. The first column looks at Reading_1 and sees if it is above a threshold, if so it labels it with a Y (named: #). The second column looks at that same Reading_1 and if the number is below the threshold it tags it with X (named:<#). A third column does an "ifand" which says if the previous # reading is a Y and the current <# is a X to mark third column clear (named: ok). What I am trying to do is take these columns in someway have them calculate the difference between the start and clear times. Basically I am looking to see if there is way to automate a calculation that looks at the ok column and if it shows ok to subtract that value from the last ok reading. So if it happens 20 timestamps back, excel finds it and makes the calculation. Any thoughts on how i can do this? Thanks!- Hide quoted text - - Show quoted text - Hi Biff, When I ran =ISNUMBER(c2) the result came up as TRUE. Here is a sample of the data I am looking at. In column labeled 1 on the example you can see at 18:13 timestamp the number is below 126 which would clear the condition so anything before it would be what I want calculated. the second column is showing the =ISNUMBER(C2) that you asked about.. Thanks for the help so far! Date / Time 1 2 11/3/2009 0:00 TRUE 128.2 128.2 11/3/2009 4:21 126.9 128.2 11/3/2009 4:44 126.9 126.9 11/3/2009 8:27 126.9 128.2 11/3/2009 8:28 126.9 126.7 11/3/2009 8:29 126.9 128 11/3/2009 11:35 126.9 126.7 11/3/2009 11:35 126.9 128 11/3/2009 11:45 128.2 128 11/3/2009 17:15 126.9 128 11/3/2009 17:15 126.9 126.7 11/3/2009 17:59 126.9 125.4 11/3/2009 18:04 126.9 126.7 11/3/2009 18:13 125.6 126.7 11/3/2009 18:29 126.9 126.7 11/3/2009 20:50 128.2 126.7 11/3/2009 20:56 128.2 128 Mark- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating with Variable Search
the return I get is "#VALUE!"
Typically, a #VALUE! error means you're trying to do a math operation on a TEXT entry. Based on the sample file I posted the error would come from: (expression)-A2. ....If A2 was a TEXT entry. But, in my sample file A2 *is not* a text entry so the formula returns the correct result. I would need to see your real data to find out what the problem is and to give me a better idea of what you're wanting to do. -- Biff Microsoft Excel MVP "markd951" wrote in message ... Hey Biff - I follow the logic here, but when I input the formula the return I get is "#VALUE!" when the calculation completes. Any thoughts? On Nov 27, 4:34 pm, "T. Valko" wrote: I have a feeling that there's more to this than you're telling me. That's why in my last reply I said: let's start with this.... So, I put together a small sample file based on your posted data. xmarkd951.xls 14kb http://cjoint.com/?lBwAdcYGwi -- Biff Microsoft Excel MVP "markd951" wrote in message ... Hey Biff- I tried the formula but it fails. I don't why when I sent the sample data to you it included a true in that stuff. It should have been this DATE / TIME V1 V2 11/3/2009 0:00 128.2 128.2 11/3/2009 4:21 126.9 128.2 11/3/2009 4:44 126.9 126.9 11/3/2009 8:27 126.9 128.2 11/3/2009 8:28 126.9 126.7 11/3/2009 8:29 126.9 128 11/3/2009 11:35 126.9 126.7 11/3/2009 11:35 126.9 128 11/3/2009 11:45 128.2 128 11/3/2009 17:15 126.9 128 11/3/2009 17:15 126.9 126.7 11/3/2009 17:59 126.9 125.4 11/3/2009 18:04 126.9 126.7 11/3/2009 18:13 125.6 126.7 Should the array still work in this case? On Nov 25, 12:58 pm, "T. Valko" wrote: Ok, let's start with this array formula** based on the posted sample data. =IF(B2<126,0,INDEX(A2:A18,MATCH(TRUE,B2:B18<126,0) )-A2) Format as [h]:mm Result = 18:13 ** 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 "markd951" wrote in message ... Hey Biff - The answers to the questions you posed... 11/3/09 0:00 is in fact the start time. 11/3/09 18:13 value is the stop time as you mentioned Yes I want to find the value between the start and stop time. All the date and time stamps will be different dates as the sheet moves along. So it would be days worth of data (11/3 to say 11/7). There will always (I hope) be a value in either column A or B that fails below threshold making a stop time possible. The thing is Column A and Column B will be independent of each other for what I am doing. So if we focus on getting column A to work I should be able to get column B going afterwards. Thanks. On Nov 23, 12:37 pm, "T. Valko" wrote: Ok, let's see if I understand this... Based on the posted sample data... 11/3/2009 0:00 is the start time 11/3/2009 18:13 is the stop time because 125.6 in column B is the first instance of a value in column B that is less than 126. Find the time difference between "stop" and "start"? Ok, a couple of questions... Are the date/time stamps all the same date? Is there *always* at least one instance where the value in column B falls below a threshold therefore making it a stop time? -- Biff Microsoft Excel MVP "markd951" wrote in message ... On Nov 21, 11:04 pm, "T. Valko" wrote: If this data is imported from another application is the date/time stamp a true Excel date/time? If C2 is a date/time stamp what is the result of this formula: =ISNUMBER(C2) Can you post a small example of your data with the expected result? Don't really need the Device/SubDevice data. Just the date/time and the X/Y data -- Biff Microsoft Excel MVP "markd951" wrote in message ... Hello everyone and I hope someone can help me on this. I am working on a spreadsheet and I am trying to automate some of the calculations in the document I am running into a sticking point and I wanted to see if anyone has an idea on how to get this to work. Issue: I am receiving data from an exported report that works within Excel. It has a date and timestamp plus some recorded data associated with that timestamp. What I have going on right now is I have the following columns in the export. | Device | SubDevice | Date / TIme | Reading_1 | What I have set right now are three columns to the right of the reading. They look at the data in each reading column and apply the following. The first column looks at Reading_1 and sees if it is above a threshold, if so it labels it with a Y (named: #). The second column looks at that same Reading_1 and if the number is below the threshold it tags it with X (named:<#). A third column does an "ifand" which says if the previous # reading is a Y and the current <# is a X to mark third column clear (named: ok). What I am trying to do is take these columns in someway have them calculate the difference between the start and clear times. Basically I am looking to see if there is way to automate a calculation that looks at the ok column and if it shows ok to subtract that value from the last ok reading. So if it happens 20 timestamps back, excel finds it and makes the calculation. Any thoughts on how i can do this? Thanks!- Hide quoted text - - Show quoted text - Hi Biff, When I ran =ISNUMBER(c2) the result came up as TRUE. Here is a sample of the data I am looking at. In column labeled 1 on the example you can see at 18:13 timestamp the number is below 126 which would clear the condition so anything before it would be what I want calculated. the second column is showing the =ISNUMBER(C2) that you asked about. Thanks for the help so far! Date / Time 1 2 11/3/2009 0:00 TRUE 128.2 128.2 11/3/2009 4:21 126.9 128.2 11/3/2009 4:44 126.9 126.9 11/3/2009 8:27 126.9 128.2 11/3/2009 8:28 126.9 126.7 11/3/2009 8:29 126.9 128 11/3/2009 11:35 126.9 126.7 11/3/2009 11:35 126.9 128 11/3/2009 11:45 128.2 128 11/3/2009 17:15 126.9 128 11/3/2009 17:15 126.9 126.7 11/3/2009 17:59 126.9 125.4 11/3/2009 18:04 126.9 126.7 11/3/2009 18:13 125.6 126.7 11/3/2009 18:29 126.9 126.7 11/3/2009 20:50 128.2 126.7 11/3/2009 20:56 128.2 128 Mark- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating with Variable Search
I am wondering if the data set returns date and time as a Text string
and not a number, I will check into this with the vendor and see what I can find out. Thanks man! On Dec 1, 12:57*pm, "T. Valko" wrote: the return I get is "#VALUE!" Typically, a #VALUE! error means you're trying to do a math operation on a TEXT entry. Based on the sample file I posted the error would come from: (expression)-A2. ...If A2 was a TEXT entry. But, in my sample file A2 *is not* a text entry so the formula returns the correct result. I would need to see your real data to find out what the problem is and to give me a better idea of what you're wanting to do. -- Biff Microsoft Excel MVP "markd951" wrote in message ... Hey Biff - I follow the logic here, but when I input the formula the return I get is "#VALUE!" when the calculation completes. Any thoughts? On Nov 27, 4:34 pm, "T. Valko" wrote: I have a feeling that there's more to this than you're telling me. That's why in my last reply I said: let's start with this.... So, I put together a small sample file based on your posted data. xmarkd951.xls 14kb http://cjoint.com/?lBwAdcYGwi -- Biff Microsoft Excel MVP "markd951" wrote in message .... Hey Biff- I tried the formula but it fails. I don't why when I sent the sample data to you it included a true in that stuff. It should have been this DATE / TIME V1 V2 11/3/2009 0:00 128.2 128.2 11/3/2009 4:21 126.9 128.2 11/3/2009 4:44 126.9 126.9 11/3/2009 8:27 126.9 128.2 11/3/2009 8:28 126.9 126.7 11/3/2009 8:29 126.9 128 11/3/2009 11:35 126.9 126.7 11/3/2009 11:35 126.9 128 11/3/2009 11:45 128.2 128 11/3/2009 17:15 126.9 128 11/3/2009 17:15 126.9 126.7 11/3/2009 17:59 126.9 125.4 11/3/2009 18:04 126.9 126.7 11/3/2009 18:13 125.6 126.7 Should the array still work in this case? On Nov 25, 12:58 pm, "T. Valko" wrote: Ok, let's start with this array formula** based on the posted sample data. =IF(B2<126,0,INDEX(A2:A18,MATCH(TRUE,B2:B18<126,0) )-A2) Format as [h]:mm Result = 18:13 ** 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 "markd951" wrote in message .... Hey Biff - The answers to the questions you posed... 11/3/09 0:00 is in fact the start time. 11/3/09 18:13 value is the stop time as you mentioned Yes I want to find the value between the start and stop time. All the date and time stamps will be different dates as the sheet moves along. So it would be days worth of data (11/3 to say 11/7). There will always (I hope) be a value in either column A or B that fails below threshold making a stop time possible. The thing is Column A and Column B will be independent of each other for what I am doing. So if we focus on getting column A to work I should be able to get column B going afterwards. Thanks. On Nov 23, 12:37 pm, "T. Valko" wrote: Ok, let's see if I understand this... Based on the posted sample data... 11/3/2009 0:00 is the start time 11/3/2009 18:13 is the stop time because 125.6 in column B is the first instance of a value in column B that is less than 126. Find the time difference between "stop" and "start"? Ok, a couple of questions... Are the date/time stamps all the same date? Is there *always* at least one instance where the value in column B falls below a threshold therefore making it a stop time? -- Biff Microsoft Excel MVP "markd951" wrote in message ... On Nov 21, 11:04 pm, "T. Valko" wrote: If this data is imported from another application is the date/time stamp a true Excel date/time? If C2 is a date/time stamp what is the result of this formula: =ISNUMBER(C2) Can you post a small example of your data with the expected result? Don't really need the Device/SubDevice data. Just the date/time and the X/Y data -- Biff Microsoft Excel MVP "markd951" wrote in message ... Hello everyone and I hope someone can help me on this. I am working on a spreadsheet and I am trying to automate some of the calculations in the document I am running into a sticking point and I wanted to see if anyone has an idea on how to get this to work. Issue: I am receiving data from an exported report that works within Excel. It has a date and timestamp plus some recorded data associated with that timestamp. What I have going on right now is I have the following columns in the export. | Device | SubDevice | Date / TIme | Reading_1 | What I have set right now are three columns to the right of the reading. They look at the data in each reading column and apply the following. The first column looks at Reading_1 and sees if it is above a threshold, if so it labels it with a Y (named: #). The second column looks at that same Reading_1 and if the number is below the threshold it tags it with X (named:<#). A third column does an "ifand" which says if the previous # reading is a Y and the current <# is a X to mark third column clear (named: ok). What I am trying to do is take these columns in someway have them calculate the difference between the start and clear times. Basically I am looking to see if there is way to automate a calculation that looks at the ok column and if it shows ok to subtract that value from the last ok reading. So if it happens 20 timestamps back, excel finds it and makes the calculation. Any thoughts on how i can do this? Thanks!- Hide quoted text - - Show quoted text - Hi Biff, When I ran =ISNUMBER(c2) the result came up as TRUE. Here is a sample of the data I am looking at. In column labeled 1 on the example you can see at 18:13 timestamp the number is below 126 which would clear the condition so anything before it would be what I want calculated.. the second column is showing the =ISNUMBER(C2) that you asked about. Thanks for the help so far! Date / Time 1 2 11/3/2009 0:00 TRUE 128.2 128.2 11/3/2009 4:21 126.9 128.2 11/3/2009 4:44 126.9 126.9 11/3/2009 8:27 126.9 128.2 11/3/2009 8:28 126.9 126.7 11/3/2009 8:29 126.9 128 11/3/2009 11:35 126.9 126.7 11/3/2009 11:35 126.9 128 11/3/2009 11:45 128.2 128 11/3/2009 17:15 126.9 128 11/3/2009 17:15 126.9 126.7 11/3/2009 17:59 126.9 125.4 11/3/2009 18:04 126.9 126.7 11/3/2009 18:13 125.6 126.7 11/3/2009 18:29 126.9 126.7 11/3/2009 20:50 128.2 126.7 11/3/2009 20:56 128.2 128 Mark- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating a daily rate when the rate could be fixed or variable | New Users to Excel | |||
How do I create a macro with a variable search feature? | Excel Discussion (Misc queries) | |||
Calculating values in two columns based on a variable | Excel Discussion (Misc queries) | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions |