rather complex logic statement

[michael.leonard@gmail.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.

[DL]

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

"michael.leonard@gmail.com" 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.
>
>

[Sandy Mann]

Michael,

Try:

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

--
HTH

Sandy
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

<michael.leonard@gmail.com> wrote in message
news:1143751756.937076.113210@i40g2000cwc.googlegroups.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.
>

[Peo Sjoblom]

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.000138888888888889;0.000173611111111111;0.000208333333333333;0.000243055555555555;0.000277777777777778;0.0003125;0.000347222222222222},{"3db";"6db";"9db";"12db";"15db";"18db";"21db";"24db";"27db";"30db";"33db"}))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

-



<michael.leonard@gmail.com> wrote in message
news:1143751756.937076.113210@i40g2000cwc.googlegroups.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.
>

[michael.leonard@gmail.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

[Peo Sjoblom]

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




<michael.leonard@gmail.com> wrote in message
news:1143767377.395788.97480@u72g2000cwu.googlegroups.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
>

[michael.leonard@gmail.com]

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

Thanks