+ Reply to Thread
Results 1 to 4 of 4

Neighbouring Numbers subtracted from one another

  1. #1
    Registered User
    Join Date
    03-15-2013
    Location
    London
    MS-Off Ver
    Excel 2016 (Mac)
    Posts
    69

    Neighbouring Numbers subtracted from one another

    Hello everyone

    I am working on a function in which neighbouring integers of a sequence are subtracted from one another. As a rule, the smaller number is subtracted from the larger one so that there are no negative values. This process continues until there are no values left.

    I am encountering two problems:

    1.
    I have to create a separate file for each new sequence because the process is cyclical. By this I mean that the last element of a sequence is brought into a subtractive process with the first element. Therefore, I have to write a function for a series with 7 numbers, with 8 numbers, with 9 numbers, etc. What I would like, ideally, is to have a single function into which I can input anything from 2 to a maximum of 30 numbers and which will always “react cyclically”, that is, no matter the amount of inputted numbers, the last value will always refer back to the first value.

    2.
    The second problem is the more complex one.
    When two neighbouring numbers are subtracted, sooner or later the result will be zero. If this happens, I would like the cell to be blank. Now, the important thing is that in the next “round” of subtractions this blank cell is “jumped over” (ignored) in favour of the next occurring number. In this way, ‘0’ (zero) is never involved in the actual process of subtraction but only positive integers. The process will then exhaust itself and come to a close.

    I have attached a file that illustrates this much better than my verbal explanation.
    On the left hand side is my own version which is “wrong” since zeros are included and the process is potentially infinite or on a loop.
    On the right-hand side is a manual reproduction of the same 10-number sequence demonstrating how I would envisage the results (and the function) to be.

    Can anyone help?
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Neighbouring Numbers subtracted from one another

    1.

    Please Login or Register  to view this content.
    In C4 and copy across and down

    =ABS(C3 - INDEX(3:3, MOD(COLUMN() - COLUMN($C$3) + 1, $C$1) + COLUMN($C$3)))

    2. That would probably require VBA.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Neighbouring Numbers subtracted from one another

    Hi,

    In your example why doesn't X4 show 1, being O3-X3 ?
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    03-15-2013
    Location
    London
    MS-Off Ver
    Excel 2016 (Mac)
    Posts
    69

    Re: Neighbouring Numbers subtracted from one another

    ah ... because I made a mistake ... and now the entire structure is obviously "off" from there on.

    Nonetheless, I hope one can still understand what I am after.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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