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
Bookmarks