Trying to work out how to generate a "DO" or "DON'T" signal from a list of numbers.
The rule is as follows
IF above 100 and previous number below 100 then "DO"
IF above -100 and pevious number below -100 then "DO
IF below 100 and previous number above 100 then "DON'T"
IF below -100 and previous number above -100 then "DON'T"
Basically looking for notification when number breaks 100 or -100 level either way.
I hope I've written that in a way that makes sense.
Thanks
Are these numbers in a column in a worksheet? If so, would you be after a formula in an adjacent column to but a value against the second an higher numbers?
Martin
Yes, numbers in a column in a worksheet, one line per day, wanting to compare latest day to previous and then give signal, as I try to show below
2.76
-40.66
101.93 create signal
243.48
265.02
80.63 create signal
-73.06
-149.72 create signal
-242.02
-139.57
-95.11 create signal
cheers John
I put this formula in A2 and copied down:
=IF( (A2 > 100) * (A1 < 100) + (A2 > -100) * (A1 < -100), "Create signal", "")
... and got this result with your data:
... which doesn't match your result.
Please Login or Register to view this content.
in A2 and down
=SIGN(100-ABS(A2))<>SIGN(100-ABS(A1))
This will return False for large transitions across 0 like (120 to -300) or (-215 to 463)
and True for (-130 to 47) or (246 to -18).
I wasn't clear on what you wanted in these cases.
This function acts as if their were two gates at -100 and 100.
If the number passes through a gate, (DO / Don't) changes state.
The big transition (200 to -200) passes through two gates (= False).
(120 to 45) and (120 to -3) each pass through only one gate (= True).
Last edited by mikerickson; 12-01-2007 at 09:59 PM.
The following formula will give you the below result.
Hope it is close to what you was looking for.
Ola
=IF(OR(AND(A2>100;A1<100);AND(A2>-100;A1<-100));"DO";IF(OR(AND(A2<100;A1>100);AND(A2<-100;A1>-100));"DON'T";"?"))
Please Login or Register to view this content.
Mike - Very good point about two gates, hadn't considered the possibility of that. I have gone with Ola's solution however as it allows me to manipulate what the signal reads rather than just True or False
Ola - Thank you, couldn't work out why your formula wouldn't work to start, but then changed all your semi colons to commas and it solved the issue. Not sure whether this is something which has changed (I'm Offfice 2007), anyway it now works perfectly. Thank you
Thank you all, I always find it interesting how there are different solutions to the same problem.
John
Happy to hear that it solved your problem.
The ";" or "," has to do with international standards. I think it all starts out with that half of the world uses "." as decimal sign and the rest of the world uses "," - that is where I'm coming from. Then to separate items we have to use ";". I think that's where it all starts but maybe someone here can enlighten us. Also in accounting the thousand separator can be "." or "," or " ".
Ok, enough trivia for one day.
Ola
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks