+ Reply to Thread
Results 1 to 16 of 16

If the difference between a row of two columns is positive write "A" , etc.

  1. #1
    Registered User
    Join Date
    05-29-2022
    Location
    POland
    MS-Off Ver
    2016
    Posts
    9

    Unhappy If the difference between a row of two columns is positive write "A" , etc.

    Hi guys
    I am trying to write simple algorithms in Excel and for one function I have difficulties.
    What I want to do is that if the difference between a row of two columns is positive write "A" and if the next row is positive it would be blank instead "A" and also for negative values. if the difference between a row of two columns is negative write "B" and if the next row is negative also it would be blank instead "B".

    =IF((AND(A2>B2, A1>B1)), "", "B"), IF((AND(A2<B2,A1<B1)),"","A")
    this is I wrote but it does not work.
    Attached Files Attached Files
    Last edited by loloobaloo; 05-29-2022 at 10:06 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,405

    Re: If the difference between a row of two columns is positive write "A" , etc.

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

    Administrative Note:

    Your thread title needs to convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. As you are new here, I have done it for you this time.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,405

    Re: If the difference between a row of two columns is positive write "A" , etc.

    =IF((AND(A2>A2, A1>A1)), "", "B"), IF((AND(A2<A2,A1<A1)),"","A")
    this is I wrote but it does not work.
    I'm not surprised! A2 cannot be larger than itself, for starters.

    When I have sight of a workbook and understand exactly what you are trying to do, I'll see if I can offer a working solution.

  4. #4
    Registered User
    Join Date
    05-29-2022
    Location
    POland
    MS-Off Ver
    2016
    Posts
    9

    Re: If the difference between a row of two columns is positive write "A" , etc.

    Thank you very much for the reply
    I made a mistake and I edited my codes.
    This is the reformed code
    =IF((AND(A2>B2, A1>B1)), "", "B"), IF((AND(A2<B2,A1<B1)),"","A")

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,405

    Re: If the difference between a row of two columns is positive write "A" , etc.

    Nonetheless, I would like to see a sample workbook, as your attempted formula does not seem to match what you are describing.

    In the workbook, mock up what you want.

    In the meantime, a complete guess:

    =IF(AND(A2>B2,A1>B1),"A", IF(AND(A2<B2,A1<B1),"B",""))

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,405

    Re: If the difference between a row of two columns is positive write "A" , etc.

    No response ...

    Does what I suggested help at all?

    Your attachment does not really help. Please mock up (manually - no formula!) the results you want.
    Last edited by AliGW; 05-29-2022 at 07:44 AM.

  7. #7
    Registered User
    Join Date
    05-29-2022
    Location
    POland
    MS-Off Ver
    2016
    Posts
    9

    Re: If the difference between a row of two columns is positive write "A" , etc.

    I uploaded the sample file.
    What I want to do is write a code that does these things
    If the difference between a row of two columns is positive write "A" and if the next row is negative write B but just for the first changes.
    For instance, if there are four cells that are positive I want to see A for the first positive value and for others to see blank cells, and if the difference would be negative for the next cells see B for the first cell, not for the second or third negative.
    I really appreciate your help.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,405

    Re: If the difference between a row of two columns is positive write "A" , etc.

    So did you try the formula I gave you? Yes or no?

    Your attachment does not really help. Please mock up (manually - no formula!) the results you want.

  9. #9
    Registered User
    Join Date
    05-29-2022
    Location
    POland
    MS-Off Ver
    2016
    Posts
    9

    Re: If the difference between a row of two columns is positive write "A" , etc.

    I uploaded the attachment.
    The result I am looking for is column F.
    I tried the formula you sent me but it didn't work as I was looking for

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,405

    Re: If the difference between a row of two columns is positive write "A" , etc.

    I'm sorry - I don't understand your description of what you want at all.

    Please talk me through the calculation you are making to determine the results of the first five rows:

    AliGW on MS365 Beta Channel (Windows 11) 64 bit

    A
    B
    C
    D
    E
    F
    1
    **02-Jan-15
    **2,058.20
    2
    1
    A A
    2
    **05-Jan-15
    **2,020.58
    4
    3
    A
    3
    **06-Jan-15
    **2,002.61
    6
    7
    B B
    4
    **07-Jan-15
    **2,025.90
    9
    12
    B
    5
    **08-Jan-15
    **2,062.14
    13
    11
    A A
    Sheet: Sheet1

  11. #11
    Registered User
    Join Date
    05-29-2022
    Location
    POland
    MS-Off Ver
    2016
    Posts
    9

    Re: If the difference between a row of two columns is positive write "A" , etc.

    I am designing an algorithm for trading according to 2 moving averages. Column C is one moving average and column D is another. I want to see in another column that shows A if values on column C are greater than column D and show B if column C is less than D. You can see column E.
    but I want more things. I want to see the first changes from positive to negative. and vice versa like column F.
    I hope I've defined clearly what I am looking for

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,405

    Re: If the difference between a row of two columns is positive write "A" , etc.

    OK - got it. Is there any way the data can start on row 2 instead of row 1?

  13. #13
    Registered User
    Join Date
    05-29-2022
    Location
    POland
    MS-Off Ver
    2016
    Posts
    9

    Re: If the difference between a row of two columns is positive write "A" , etc.

    You are right. the first row is the title of columns but because my sample should have a maximum of 20 rows and I am considering moving averages of 20 and 50 periods there wouldn't be any value for a maximum of 20 values.
    I am sorry if I made you confused

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,405

    Re: If the difference between a row of two columns is positive write "A" , etc.

    In E2 copied down:

    =IF(AND(C1<=D1,C2>D2),"A",IF(AND(C1>=D1,C2<D2),"B",""))
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    05-29-2022
    Location
    POland
    MS-Off Ver
    2016
    Posts
    9

    Re: If the difference between a row of two columns is positive write "A" , etc.

    Thanks a million.
    This is exactly what I was looking for

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,405

    Re: If the difference between a row of two columns is positive write "A" , etc.

    Glad to have helped.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

+ 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. Right function, Left function, Mid function to extract values in column A
    By bjnockle in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 06-14-2020, 03:14 PM
  2. How to combine the WORKDAY function with an IF function to build a working Gantt chart.
    By roomaggoo in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 07-07-2018, 11:54 AM
  3. COUNTUNIQUE Function in Google Sheets; Excel lacks a direct counterpart to this function?
    By PivotTablePSHomage in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-18-2018, 05:27 AM
  4. Calling function inside function. (aka nested function)
    By jakopak in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2015, 05:58 AM
  5. Replies: 13
    Last Post: 04-08-2014, 05:46 AM
  6. Replies: 1
    Last Post: 03-21-2012, 11:22 AM
  7. [SOLVED] Excel - User Defined Function Error: This function takes no argume
    By BruceInCalgary in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2006, 04:05 PM

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