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 -