IF or VBA?

[Smasen91]

Hi,
I've tried for days to get this right, but I can't figure it out, and desperately need some help. I wish I could do it with a simple formula but maybe I have to use VBA? Se Excel sheet.

When row 1 is between 0 and 30, row 4 should be 1 until row 1 goes over 50, at which time row 4 turns to zero.
When row 1 is between 70 and 100, row 4 should be -1 until row 1 goes belove 50, at which time row 4 turns to zero.

Please see row 4 (values only) to understand. I need to write a function that can fill out row 4 automatically. Row 4 is only an example of how it should look like when the formula is done.

Quick explanation:
30 and 70 (values of the first row) is the breakpoints for the second row to shift signs to 1 and -1, while 50 is the breakpoint for the second row to shift back to 0, and then stay 0, until the first row goes under 30 or over 70 again.

Detailed explanation:
When the value of the first row goes from above 30 to under 30, the value of the second row turns from 0 to 1. The value of the second row then stays 1 until the value of the first row goes above 50. When the first row moves above 50, the value of the second row should shift from 1 to 0. The second row stays 0 until the value of the first row goes above 70 (when it should turn to -1) or under 30 again (when it should turn 1 like explained already). If the first row goes above 70, the second row should stay -1 until the first row goes under 50 again, at which time the second row should turn to 0, and then stay 0 as long as the first row stays between 30 and 70. 30 and 70 (values of the first row) is the breakpoints for the second row to shift signs to 1 and -1, while 50 is the breakpoint for the second row to shift back to 0, and then stay 0, until the first row goes under 30 or over 70 again.


Look at this link: Here you have som examples. People tried to help. Can you please help me? 
http://www.excelforum.com/excel-form...a-i-guess.html

[loginjmor]

Hi -

From what you have written, and from the data in your spreadsheet, it appears 40 and 70 are your breakpoints:

X<40 Row 4 =1
X>40 and X<70 Row 4 = 0
X>70 Row 4 = -1

So just use this formula:

=IF(B1<41,1,IF(B1<71,0,-1))

Attached is a copy of your spreadsheet with this formula below the data you have entered so you can compare the formula output to your supplied data.

Hope this helps.

[Smasen91]

Hi!
Thanks, but that formula does not work. Ref. S2 does not match. You see? That cell should also be 1, because it's still above 50, after it turned 70 in cell Q1.

Hope you can help me!
Thanks again.

[loginjmor]

Hi -

OK, I didn't understand previously that the value depends on the previous value in the adjacent column. After re-reading your post, I think I understand now. So I added a couple of AND statements to match the criteria given. The formula looks like this:

=IF(B1<=30,1,IF(B1>=70,-1,IF(AND(A1<50,B1>=50),0,IF(AND(A1>50,B1<=50),0,A5))))

It checks the upper and lower ranges first and then checks to see if the series crosses 50 either on the way up or on the way down.

Attached is a spreadsheet with the above formula. It matches the given data. Please put in some additional data to make sure the formula is working correctly.

Hope this helps.