Complex IF formula.

Hi everyone. Take a quick look at this from my first thread:

Excel 2010
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
2 54 43 29 35 40 56 53 65 71 75 45 28 35 51 60 71 72 52 49 48 29 25 28 35 51
3 0 0 1 1 1 0 0 0 -1 -1 0 1 1 0 0 -1 -1 -1 0 0 1 1 1 1 0


Worksheet Formulas
Cell Formula
B3 =IF(OR(AND(B2>=30,B2<50,A3=1),B2<=30),1,IF(OR(AND(B2>=70,B2<100),AND(B2>=50,A3=-1)),-1,0))
---------------------------------------------------------------------------------------------------------------------------------------------------------------

Problem

Can you maybe help med to change this formula? It's the same rules and structure. However, I want the signals (-1,1,0) to be given the day after and not the same day as above.
So with kind of a lag. (See excel sheet for example of how it should be. Here you also find the formula). I guess it should be possible to make a little change of the formula?

So at the day it gives over/under 70/30 it should just be 0, and the signal should come the day after. Thanks!!! :D :D

Hi! Im sorry to bump this up again. I hope it's not that difficult? Please help. Thank you so much!

Originally Posted by Smasen91

Worksheet Formulas
Cell Formula
B3 =IF(OR(AND(B2>=30,B2<50,A3=1),B2<=30),1,IF(OR(AND(B2>=70,B2<100),AND(B2>=50,A3=-1)),-1,0))
---------------------------------------------------------------------------------------------------------------------------------------------------------------

Problem

Can you maybe help med to change this formula? It's the same rules and structure. However, I want the signals (-1,1,0) to be given the day after and not the same day as above.
So with kind of a lag. (See excel sheet for example of how it should be. Here you also find the formula). I guess it should be possible to make a little change of the formula?

So at the day it gives over/under 70/30 it should just be 0, and the signal should come the day after. Thanks!!! :D :D

As far as I am aware you can only place the results in the "Active cell" i.e, the one with the formula in , so unless the test can be changed to look retrospectively, you may have to resort to VBA.

Hmm. I can't use VBA
But what do you mean with look retrospectively?

Maybe it's a way to "delete" every first -1, or 1 in a row... Hmm. That will also work.

BY retrospectively I mean value for Day n is determined by logic relating to Day n-1.

I see. That's how it should be.
Do you have an tip of how I can look at it that way? haha Thanks!

If I did I would have offered it!

Im sorry. Of course.
Im a little desp. Hmm.

Im hoping it's maybe some other people who knows...

Maybe if I make a new sheet... Where I use my ordinary one.. And said something like this:
If A1 is zero or blank and A2 is 1 or -1, delete that 1, or -1.

Do you follow my thought John? Because 1 or -1 will always begin after a zero or blank, and it is only the first one I need to delete.

As John suggested, make you formula retrospective. Change your formula to look at the cell to the left of the one it is currently looking at.
Referencing your example. Have the formula in C4 look at B1. Copy your formula across. All the -1, 0, 1 line up they way you want the.

Just a thought

Of course! Thanks! :D