Hi All,

I'm a little bit of a noob but I can't seem to find my answer anywhere
so here it goes.
I'm in a sleep research lab. One of our experiments has an element
where we wake the
person with a tone that increases by 3db every 3 seconds over a total
of 33 seconds.

So with this data I'm trying to take a time variable form one column
and translate it into a categorical value in another.

Let me illustrate:
J4 is the clock time that we start the tone
K4 is the clock time that it wakes the person
L4 in the elapsed time it took to wake the person
(all of these work great, very basic stuff)
M4 is the category that L4 falls into based on elapsed time

so if L4 is =00:00:00 and <=00:00:03, M4 should = "3db"
or
if L4 is =00:00:03 and <=00:00:06, M4 should = "6db"

and so on through a total of 11 ranged conditions 3db-33db.

Thing is you can only nest 7 "IF" statements. So that's out.

Any help would be great, I am very much stuck.

Michael:

This may be a bit of a clumsy solution, but here goes:

Can you split your logic into more than one cell? For example have three If
conditions that return a code and then reference that cell with some
additional statements to generate the categorical variable?

Doug

" wrote:

Hi All,

I'm a little bit of a noob but I can't seem to find my answer anywhere
so here it goes.
I'm in a sleep research lab. One of our experiments has an element
where we wake the
person with a tone that increases by 3db every 3 seconds over a total
of 33 seconds.

So with this data I'm trying to take a time variable form one column
and translate it into a categorical value in another.

Let me illustrate:
J4 is the clock time that we start the tone
K4 is the clock time that it wakes the person
L4 in the elapsed time it took to wake the person
(all of these work great, very basic stuff)
M4 is the category that L4 falls into based on elapsed time

so if L4 is =00:00:00 and <=00:00:03, M4 should = "3db"
or
if L4 is =00:00:03 and <=00:00:06, M4 should = "6db"

and so on through a total of 11 ranged conditions 3db-33db.

Thing is you can only nest 7 "IF" statements. So that's out.

Any help would be great, I am very much stuck.

Michael,

Try:

=CEILING(L4,TIME(0,0,3))/TIME(0,0,3)*3 & "db"

--
HTH

Sandy

with @tiscali.co.uk

wrote in message
oups.com...
Hi All,

I'm a little bit of a noob but I can't seem to find my answer anywhere
so here it goes.
I'm in a sleep research lab. One of our experiments has an element
where we wake the
person with a tone that increases by 3db every 3 seconds over a total
of 33 seconds.

So with this data I'm trying to take a time variable form one column
and translate it into a categorical value in another.

Let me illustrate:
J4 is the clock time that we start the tone
K4 is the clock time that it wakes the person
L4 in the elapsed time it took to wake the person
(all of these work great, very basic stuff)
M4 is the category that L4 falls into based on elapsed time

so if L4 is =00:00:00 and <=00:00:03, M4 should = "3db"
or
if L4 is =00:00:03 and <=00:00:06, M4 should = "6db"

and so on through a total of 11 ranged conditions 3db-33db.

Thing is you can only nest 7 "IF" statements. So that's out.

Any help would be great, I am very much stuck.

You can use a lookup table although I can't see how you can have both

=00:00:00 and <=00:00:03

and

=00:00:03 and <=00:00:06

it should be either

=00:00:00 and <00:00:03

and

=00:00:03 and <00:00:06

or

=00:00:00 and <=00:00:03

and

00:00:03 and <=00:00:06

assuming you mean less than 3 seconds for the first limit, then use a 2
column table like

0 3db
00:00:03 6db
00:00:06 9db
00:00:09 12db
00:00:12 15db
00:00:15 18db
00:00:18 21db
00:00:21 24db
00:00:24 27db
00:00:27 30db
00:00:30 33db

then simply use

=IF(L4="","",LOOKUP(L4,A1:A11,B1:B11))

where A1:B11 is the table, hardcoded it will be very ugly

=IF(L4="","",LOOKUP(L4,{0;0.0000347222222222222;0. 0000694444444444444;0.000104166666666667;0.0001388 88888888889;0.000173611111111111;0.000208333333333 333;0.000243055555555555;0.000277777777777778;0.00 03125;0.000347222222222222},{"3db";"6db";"9db";"12 db";"15db";"18db";"21db";"24db";"27db";"30db";"33d b"}))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

-



wrote in message
oups.com...
Hi All,

I'm a little bit of a noob but I can't seem to find my answer anywhere
so here it goes.
I'm in a sleep research lab. One of our experiments has an element
where we wake the
person with a tone that increases by 3db every 3 seconds over a total
of 33 seconds.

So with this data I'm trying to take a time variable form one column
and translate it into a categorical value in another.

Let me illustrate:
J4 is the clock time that we start the tone
K4 is the clock time that it wakes the person
L4 in the elapsed time it took to wake the person
(all of these work great, very basic stuff)
M4 is the category that L4 falls into based on elapsed time

so if L4 is =00:00:00 and <=00:00:03, M4 should = "3db"
or
if L4 is =00:00:03 and <=00:00:06, M4 should = "6db"

and so on through a total of 11 ranged conditions 3db-33db.

Thing is you can only nest 7 "IF" statements. So that's out.

Any help would be great, I am very much stuck.

I see what your getting at.
Bang on about the correction on the ranges, oops ;-)
One thing that I should have mentioned is that the time value in L4 is
generated from the two preceding time stamps. So there is a very good
chance that L4 will have a time of 00:00:05 in it. That's why I was
trying to write the conditional statements. Will the lookup table take
that into account? Seems to me it only corilates the value to it's
maching db value. But then again I am new to this

Thanks again for the help I apreciate it

Try a little sample, if the value is 1 second it will lookup up the lookup
value and if not found the largest value smaller than the lookup value so
from 0 to 2.999 seconds it will lookup the zero and return 3Db, if 00:00:03
or greater but less than 00:00:06 it will lookup 00:00:03 and return 6Db and
so on. The only thing you need to do is to set the where you want that value
to be, I assumed 0 - < 00:00:03 and so on. If it would be <= 00:00:03 then
you would need to adjust to the smallest value you could use greater than
00:00:03 like 00:00:03.001

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




wrote in message
ups.com...
I see what your getting at.
Bang on about the correction on the ranges, oops ;-)
One thing that I should have mentioned is that the time value in L4 is
generated from the two preceding time stamps. So there is a very good
chance that L4 will have a time of 00:00:05 in it. That's why I was
trying to write the conditional statements. Will the lookup table take
that into account? Seems to me it only corilates the value to it's
maching db value. But then again I am new to this

Thanks again for the help I apreciate it

Works like a charm. Thanks for also elaborating on the lookup function
and how it operates.

Thanks