+ Reply to Thread
Results 1 to 19 of 19

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

  1. #1
    Registered User
    Join Date
    03-14-2016
    Location
    Oslo
    MS-Off Ver
    2013 Student Package
    Posts
    47

    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.
    Attached Files Attached Files
    Last edited by Smasen91; 04-13-2016 at 04:31 PM.

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,829

    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?
    If someone has helped you then please add to their Reputation

  3. #3
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,829

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

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

  4. #4
    Registered User
    Join Date
    03-14-2016
    Location
    Oslo
    MS-Off Ver
    2013 Student Package
    Posts
    47

    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. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

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

    with #1 try in A4:
    Formula: copy to clipboard
    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
    Last edited by sandy666; 04-13-2016 at 02:53 PM. Reason: typo
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  6. #6
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,829

    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?

  7. #7
    Registered User
    Join Date
    03-14-2016
    Location
    Oslo
    MS-Off Ver
    2013 Student Package
    Posts
    47

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

    Correct.

  8. #8
    Registered User
    Join Date
    03-14-2016
    Location
    Oslo
    MS-Off Ver
    2013 Student Package
    Posts
    47

    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. #9
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

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

    Enter in B4 and fill right


    Formula: copy to clipboard
    Please Login or Register  to view this content.
    My Rules if you want my help:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  10. #10
    Registered User
    Join Date
    03-14-2016
    Location
    Oslo
    MS-Off Ver
    2013 Student Package
    Posts
    47

    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. #11
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    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. #12
    Registered User
    Join Date
    03-14-2016
    Location
    Oslo
    MS-Off Ver
    2013 Student Package
    Posts
    47

    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.
    Attached Files Attached Files
    Last edited by Smasen91; 04-13-2016 at 03:27 PM.

  13. #13
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    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. #14
    Registered User
    Join Date
    04-13-2016
    Location
    Oslo, Norway
    MS-Off Ver
    Office 13
    Posts
    2

    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. #15
    Registered User
    Join Date
    03-14-2016
    Location
    Oslo
    MS-Off Ver
    2013 Student Package
    Posts
    47

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

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

  16. #16
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

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

    Try this

    Enter formula in A2 and copy across
    Formula: copy to clipboard
    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
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  17. #17
    Registered User
    Join Date
    03-14-2016
    Location
    Oslo
    MS-Off Ver
    2013 Student Package
    Posts
    47

    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. #18
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

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

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

  19. #19
    Registered User
    Join Date
    04-13-2016
    Location
    Oslo, Norway
    MS-Off Ver
    Office 13
    Posts
    2

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Honoluuluu, guess where I'm from..
    By honoluuluu in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 02-05-2015, 06:52 PM
  2. I guess it's time .... Thank you all
    By JP Romano in forum The Water Cooler
    Replies: 11
    Last Post: 06-18-2014, 07:51 PM
  3. [SOLVED] Accounting i guess
    By Duggie06 in forum Excel General
    Replies: 10
    Last Post: 04-17-2012, 12:40 PM
  4. Have a guess
    By pike in forum The Water Cooler
    Replies: 4
    Last Post: 01-18-2012, 11:05 AM
  5. So I guess everyone can become an Excel MVP these days
    By JieJenn in forum The Water Cooler
    Replies: 18
    Last Post: 01-03-2012, 05:24 PM
  6. Excel Woes - Formula bug (I guess)
    By mikeydread1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-18-2006, 11:50 AM
  7. IRR Guess Problem
    By Dkline in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-07-2005, 09:40 AM

Tags for this Thread

Bookmarks

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