# Need help with complex IF formula I guess?? :)

1. ## Need help with complex IF formula I guess?? :)

Attachment 455946

Hi,
I've tried for days to get this right, but I can't figure it out, and desperately need some help.

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.

2. ## Re: Need help with complex IF formula I guess?? :)

The logic doesnt make sense.

your 1st statement says "between 0 and 30" but then talks about the answer being 1 until 50? I assume the 30 is a typo and should be 50

your 2nd statement however says that the answer is -1 until row 1 goes below 50 in which case it becomes 0? that contradicts the 1st statement about values < 50 giving 1 as the answer?

0 - 30: 1
31 - 50: ?
51 - 69: 0
70 - 100: -1
100+ : ??

4. ## Re: Need help with complex IF formula I guess?? :)

It's only values between 0-100.

I need a formula that says if the value goes below 30 set 1 (Set 1 between 0-49) until it hits 50. If it hits 50 set 0. Until either it hits under 30 again (do the same thing as before) or goes over 70. If it goes over 70 set -1 ( and keep it -1 if the value is between 70-100) and keep it -1 until it hits below 50 again. So basically it can be 0 or 1 between 31-50 and 51-69. It depends on the direction it came from (70 and hits 50 down, or 30 and hits 50 up.)

Try to look at the numbers in the picture to get a better understanding. Did it help?

Thank you so much!

5. ## Re: Need help with complex IF formula I guess?? :)

with #1 try in A4:
Formula:
`Please Login or Register  to view this content.`
and drag to the right
I assumed:
0-29......1
30-70 ....0
71-100...-1

Edited because of post #4

6. ## Re: Need help with complex IF formula I guess?? :)

When you say it depends on direction it came from, do you mean the value before it? ie if D1 is 40 and E1 = 50 then its gone up?

Correct.

8. ## Re: Need help with complex IF formula I guess?? :)

Hi Sandy 666!
Hmmm. =CHOOSE(MATCH(A1,{0,29,71},1),1,0,-1) doesn't seem to work?

See picture with your formula, compared to the one above. But I think you are into something..... Excel 2.png

9. ## Re: Need help with complex IF formula I guess?? :)

Enter in B4 and fill right

Formula:
`Please Login or Register  to view this content.`

10. ## Re: Need help with complex IF formula I guess?? :)

Hello my friend.

You can't use the 0,1,-1 values that I posted. It's just an example of how I want it to look like when the formula is correct.

Or did I misunderstand your formula now?

Thanks.

11. ## Re: Need help with complex IF formula I guess?? :)

Sorry, but I don't see the pictures. I tried recognize from your text but the best way is add example file.xlsx not a picture.
regards

12. ## Re: Need help with complex IF formula I guess?? :)

I try to upload the excel file but it does not work? I try do click on the ad attachment checkbox, but it does not respond. hmm.

13. ## Re: Need help with complex IF formula I guess?? :)

edit last post (or make new)
go to Advanced
Scroll down
Click on Manage Attachments and do the rest...

14. ## Re: Need help with complex IF formula I guess?? :)

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.

15. ## Re: Need help with complex IF formula I guess?? :)

Thanks. :D
See excel file in post 12 now + explanation from mackavoy below.

16. ## Re: Need help with complex IF formula I guess?? :)

Try this

Enter formula in A2 and copy across
Formula:
`Please Login or Register  to view this content.`

 v A B C D E F G H I J K L M N 1 54 43 29 35 40 56 53 65 71 75 45 28 35 51 2 0 0 1 1 1 0 0 0 -1 -1 0 1 1 0 3

17. ## Re: Need help with complex IF formula I guess?? :)

Hi AlKey!
Thanks.
I think you are into something here. However, if I extend the numbers in row 1 (Because it's really long actually) It doesn't seem to work anymore??

18. ## Re: Need help with complex IF formula I guess?? :)

"It doesn't seem to work anymore??" what does it mean?

19. ## Re: Need help with complex IF formula I guess?? :)

54 43 29 35 40 56 72 29 61 75 40 28 35 51
1 0 1 1 1 0 -1 1 0 -1 1 1 1 0

At first your function worked, but when we started changing the values in the first row, sometimes the formula gives the wrong value in the second row.

For example: value 40 in the first row resulted in value 1 in the second row (should be zero) when we changed the values in the first row like we did above.

Why doesn't it work? We don't quite understand your function, but as long as it works, we're (me and Smasen1) fine with it and very grateful

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1