Proper If formula syntax for above and below number

Try this in B4...

=IF(AND(OR(A4=1,A4=-1),MOD(SUM($A$4:A4),11)),A4,IF(B1<30,1,IF(B1>70,-1,0)))

Copy across

Hello again. I repost this because it was locked down.

I was wondering if you could help me with this one.

See the blue line in excel sheet of how I want it to look like after using a formula. I tried to put in a formula, but I just receive #Name. I want the formula to say:

- If column 1 goes below 30, set 1 the next 10 days. No matter which 10 numbers you have next in column 1.
- If column 1 goes over 70, set -1 the next 10 days. No matter which 10 numbers you have next in column 1.
- If not, set 0.
- The row is really long so I want to draw it further in the originally excel sheet.

Thank you so much!! :D

I assume by column to mean row. What is SUMMER which gives #NAME problem?

Try this ..

=IF(AND(OR(A5=1,A5=-1),MOD(SUM($A$5:A5),11)),A5,IF(B1<30,1,IF(B1>=70,-1,0)))

a little shorter...
=IF(A5*MOD(SUM($A$5:A5),11),A5,IF(B1<30,1,IF(B1>=70,-1,0)))

Hi again.
I have a new problem. It's my mistake because I did not say this earlier. But sometimes you will have empty cells. And in the originally sheet I have to drag it rightways and down. So I cant lock the A7 cell
totally. I guess the way I did it now is the right way? However, because of the empty cells I receive a #Value Mistake. Do you know how to fix this in still have the same forumla? Because the formula is awesome! :D

Hi John!
Please see my newest post! :D

The formula works perfect! The problem is that it's too complicated to use over really big cross sectional excel sheet. Where it have to get numbers from other sheets and stuff. I have an i5 but it have to calculate around 100 000 numbers....