+ Reply to Thread
Results 1 to 19 of 19

3s and 1s-Adding until the number changes

  1. #1
    Registered User
    Join Date
    01-02-2019
    Location
    Massachusetts
    MS-Off Ver
    2007
    Posts
    30

    3s and 1s-Adding until the number changes

    Need some assistance. Im fairly good with formulas as well as excel but this one seems to keep tripping me up.

    I am trying to figure out a formula that will add the numbers in a row to be added up until the number changes. When it does change then begin adding those numbers up until it changes again....and on and on and on.

    So the conditions of the number are based on the two adjacent numbers. So if it help here are the only possible combinations (111,113,133,333,331,311). Also, in the entire worksheet, the first and last numbers will always be a 3.

    Here in lies the problem or rather several problems.

    The numbers are either a 3 or a 1. EX--> 3 3 3 3 1 1 1 1 3 1 3 3 1 1 1 1 1 3 Now using this as the example here is what I have been trying for the last several days to do....

    Part one:
    1. Count (add up) the 3s until you get to a one. Once the number changes to a one then add 1 to the sum of the threes. Even if it a 3 between two 1s. So 3 3 1 3 1 3 3 3 1 3 would come out to 7 1 4 1 10 1 4
    2. Do the same for the 1s except do not add an additional 1 to the total.

    Using these conditions the end result should look like this for the example above 3 3 3 13 1 1 1 4 4 1 3 7 1 1 1 1 5 4

    Part two:
    Here is where is get's a little complicated too....(If this makes it a little bit more complicated then I can do without it but it would be great if it was possible.)
    1. Once the math is done I need all of the numbers that are still a 3 or 1 to the left of a large number to be changed to a zero without affecting the calculations from part one. Taking in to consideration that the total of the 1s might be a 3.
    2. In between each total I would like to insert the letters d or s and a comma (xd, xs, xd, xs (with x being the number))

    So once all is said and done the example would look like this --> 0 0 0 13 0 0 0 4 4 0 1 0 7 0 0 0 0 5 4
    Any help will be greatly appreciated.

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: 3s and 1s-Adding until the number changes

    Hi,

    Quote Originally Posted by Shades714 View Post
    EX--> 3 3 3 3 1 1 1 1 3 1 3 3 1 1 1 1 1 3
    Assuming those 18 numbers are in A2:A19, this array formula** in B2:

    =IF(A2<>A3,A2*(ROWS(A$1:A2)-MATCH(1,0/(A$1:A2<>A2)))+(A2=3),A2)

    and copied down as required, gives you the results you request here:

    Quote Originally Posted by Shades714 View Post
    Using these conditions the end result should look like this for the example above 3 3 3 13 1 1 1 4 4 1 3 7 1 1 1 1 5 4
    Not sure I understand this next part, though:

    Quote Originally Posted by Shades714 View Post
    1. Once the math is done I need all of the numbers that are still a 3 or 1 to the left of a large number to be changed to a zero without affecting the calculations from part one. Taking in to consideration that the total of the 1s might be a 3.
    Quote Originally Posted by Shades714 View Post
    So once all is said and done the example would look like this --> 0 0 0 13 0 0 0 4 4 0 1 0 7 0 0 0 0 5 4
    There are 19 numbers in your final desired result, yet only 18 in the original list. Can you clarify?

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: 3s and 1s-Adding until the number changes

    Not as concise as XOR LX's suggestion, but a non-array formula.

    Same assumption, numbers listed in A2:A19, enter in B2 and fill down. This goes straight to the final example in post #1, assuming that the '1' as the 11th value is an error.

    =IF(A2=A3,0,SUMIF(A$2:A2,A2,A$2:A2)+(A2=3)-SUMIF(A$1:A1,A2,B$1:B1)+IF(A2=3,COUNTIFS(A$1:A1,3,B$1:B1,">0")))

    To do the same with XOR LX's array formula, simply change the last A2 to 0.

    Not following on the part about adding d and s after certain numbers though, maybe with conditional formatting, but without an example result no idea what is expected.

  4. #4
    Registered User
    Join Date
    01-02-2019
    Location
    Massachusetts
    MS-Off Ver
    2007
    Posts
    30

    Re: 3s and 1s-Adding until the number changes

    Maybe this might clarify.....
    original look as the example is: 3 3 3 3 1 1 1 1 3 1 3 3 1 1 1 1 1 3

    3+3+3+3(+1)=13; 1+1+1+1=4; 3(+1)=4; 1; 3+3 (+1)=7; 1+1+1+1+1=5; 3(+1)

    so the example becomes 3, 3, 3, 13, 1, 1, 1, 4, 4, 1, 3, 7, 1, 1, 1, 1, 5, 4

    to help out below is the first 20x5 cells and below it is what I am aiming for:

    3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3
    3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3
    3 3 1 1 1 1 1 1 1 3 3 1 1 3 1 1 1 1 1 1
    3 3 1 1 1 1 1 1 1 1 3 1 1 3 1 1 1 1 3 3
    3 3 1 1 1 1 1 1 1 1 3 3 1 1 3 1 1 3 3 3

    3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 21
    3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 21
    3 7 1 1 1 1 1 1 7 3 7 1 2 4 1 1 1 1 1 6
    3 7 1 1 1 1 1 1 1 8 4 1 2 4 1 1 1 4 3 7
    3 7 1 1 1 1 1 1 1 8 3 7 1 2 4 1 2 3 3 10

    as for the second part which I admit is confusing, it would be look like this:

    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 21
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 21
    0 7 0 0 0 0 0 0 7 0 7 0 2 4 0 0 0 0 0 6
    0 7 0 0 0 0 0 0 0 8 4 0 2 4 0 0 0 4 0 7
    0 7 0 0 0 0 0 0 0 8 0 7 0 0 4 0 2 0 0 10

    don't worry about the d and s as I should be able to figure that out once I can figure out the numbers part.

    as or the presume error on 11th digit:

    3 3 3 3 1 1 1 1 3 1 3 3 1 1 1 1 1 3 changing to 0 0 0 13 0 0 0 4 4 0 1 0 7 0 0 0 0 5 4. It is not but I went over it again to be sure and the zero before it is an error. 3 6 9 13 1 1 1 4 4 1 3 7 1 1 1 1 5 4 which converts to 0 0 0 13 0 0 0 4 4 1 0 7 0 0 0 0 5 4. Sorry for the mixup.

    I am a little confused to the formulas but realized I did not mention one detail. row 1 dose not take in to consideration row 2 and visa versa.
    Last edited by Shades714; 01-02-2019 at 12:52 PM.

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: 3s and 1s-Adding until the number changes

    Check results of 1st and 2nd rows please. 21 or 61?

    With the source table in B2:U6, try this formula in B8 and fill to U12

    =IF(B2=C2,0,SUMIF($B2:B2,B2,$B2:B2)+(B2=3)-SUMIF($A2:A2,B2,$A8:A8)+IF(B2=3,COUNTIFS($A2:A2,3,$A8:A8,">0")))

  6. #6
    Registered User
    Join Date
    01-02-2019
    Location
    Massachusetts
    MS-Off Ver
    2007
    Posts
    30

    Re: 3s and 1s-Adding until the number changes

    Not sure why my previous post did not....well....post.

    I used your formula but ran into a problem so I took out the restrictive symbol "$" which helped a bit but still getting conflicting results. Also, I think the changing numbers to zero is not helping so is it possible to take it out of the equation.

    This is an actual line (first 20 cells of line 30 to be exact)
    3 3 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 3 1

    What your formula did
    4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 4 1 4

    What it should have looked like
    3 7 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 16 4 1

  7. #7
    Registered User
    Join Date
    01-02-2019
    Location
    Massachusetts
    MS-Off Ver
    2007
    Posts
    30

    Re: 3s and 1s-Adding until the number changes

    and not to get political but I noticed your location and just wanted to know if the English are enjoying May as much as Americans enjoy Trump. My job has me going to Europe all the time and got the chance to meet Speaker Bercow....interesting man.
    Also How in the world do I get pork pies and pasties shipped to the States...they are addictive.

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: 3s and 1s-Adding until the number changes

    Not sure what you did, with your sample data, I got the exact same results as you did, with the exception of the first 2 rows returning 61, where you said they should be 21

    Taking out the $ symbols will cause chaos with the formulas, they are needed to make them work properly. (See attached workbook)

    Businesses run more smoothly in the absense of management, maybe a country in the absence of politicians
    Pork pies 'n pasties, probably just another import blocked by Mr Trump
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-02-2019
    Location
    Massachusetts
    MS-Off Ver
    2007
    Posts
    30

    Re: 3s and 1s-Adding until the number changes

    working it now but considering it is 147x274 I'm adjust for size. Will send you a message when I've got it working right.

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: 3s and 1s-Adding until the number changes

    You shouldn't need to adjust anything, just add the formula to an empty cell below the data, then drag it right as far as the last column of data and down as many rows as needed.

    Note that it will not work if your data starts in column A (row labels in column A are ok) If your data starts in column A, you will need to insert an empty column, then enter the formula into column B.

  11. #11
    Registered User
    Join Date
    01-02-2019
    Location
    Massachusetts
    MS-Off Ver
    2007
    Posts
    30

    Re: 3s and 1s-Adding until the number changes

    I did but it reset the info so it only counted 20 then rest then read the next 20.

  12. #12
    Registered User
    Join Date
    01-02-2019
    Location
    Massachusetts
    MS-Off Ver
    2007
    Posts
    30

    Re: 3s and 1s-Adding until the number changes

    So I figured it out. It was on my end. When I transferred your formula I didn't account for the parsed cells being off to the right by 3 cells vs. your's that had it below. So I recreated the solution to show below the original grid and it worked. Thank you. very much. Next time I'm in the Reading or Credenhill area drinks are on me.

  13. #13
    Registered User
    Join Date
    01-02-2019
    Location
    Massachusetts
    MS-Off Ver
    2007
    Posts
    30

    Re: 3s and 1s-Adding until the number changes

    So it's working but I have realized I left one thing out and it's why the results were off. Each line alternates. IE. odd are calculated left to right (which is working fine) but the even lines are backwards (right to left). I tried to alter the formula to account for that but it messed it up. Suggestion.

  14. #14
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: 3s and 1s-Adding until the number changes

    For the right to left formula, all of the B2's need to refer to the last column of data.
    All of the A2's need to refer to the column immediately to the right of the last column of data.
    All of the A8's need to refer to the column immediately to the right of the formula.
    C2 needs to refer to the column immediately to the left of the last column of data.

    All $ symbols need to remain in their existing positions.

    As you have already copied it down in left to right format, the row numbers should be correct, you just need to change the columns.

    So if for example, your last column of data was column Z and the formula was going into row 50 change the original formula, from

    =IF(B2=C2,0,SUMIF($B2:B2,B2,$B2:B2)+(B2=3)-SUMIF($A2:A2,B2,$A8:A8)+IF(B2=3,COUNTIFS($A2:A2,3,$A8:A8,">0")))

    to

    =IF(Z3=Y3,0,SUMIF($Z3:Z3,Z3,$Z3:Z3)+(Z3=3)-SUMIF($AA3:AA3,Z3,$AA9:AA9)+IF(Z3=3,COUNTIFS($AA3:AA3,3,$AA50:AA50,">0")))

    I find it helps to use notepad when doing changes like this, so that you can have an original to look at for reference and one to edit.

  15. #15
    Registered User
    Join Date
    01-02-2019
    Location
    Massachusetts
    MS-Off Ver
    2007
    Posts
    30

    Re: 3s and 1s-Adding until the number changes

    ??? not working. it changes eveything to zeros. And to make sure I am understanding correctly once I transpose the formula into the grid it reads out like this.
    The grid is B2 to ER275
    TheL to R is working but I am still having problems with R to L.

    CELL B2
    =IF(B2=C2,0,SUMIF($B2:B2,B2,$B2:B2)+(B2=3)-SUMIF($A2:A2,B2,$A281:A281)+IF(B2=3,COUNTIFS($A2:A2,3,$A281:A281,">0")))

    CELL B3
    =IF(ER3=EQ3,0,SUMIF($ER3:ER3,ER3,$ER3:ER3)+(ER3=3)-SUMIF($ES3:ES3,ER3,$A
    :A282)+IF(ER3=3,COUNTIFS($ES3:ES3,3,$A282:A282,">0")))

  16. #16
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: 3s and 1s-Adding until the number changes

    The errors that stand out.

    =IF(ER3=EQ3,0,SUMIF($ER3:ER3,ER3,$ER3:ER3)+(ER3=3)-SUMIF($ES3:ES3,ER3,$A:A282)+IF(ER3=3,COUNTIFS($ES3:ES3,3,$A282:A282,">0")))

    Should be

    =IF(ER3=EQ3,0,SUMIF($ER3:ER3,ER3,$ER3:ER3)+(ER3=3)-SUMIF($ES3:ES3,ER3,$ES282:ES282)+IF(ER3=3,COUNTIFS($ES3:ES3,3,$ES282:ES282,">0")))

    Enter that in ER282, then fill left

    I haven't checked for any more errors.

  17. #17
    Registered User
    Join Date
    01-02-2019
    Location
    Massachusetts
    MS-Off Ver
    2007
    Posts
    30

    Re: 3s and 1s-Adding until the number changes

    I'm trying to attach for you to look at but it doesn't seem to want to attach.

  18. #18
    Registered User
    Join Date
    01-02-2019
    Location
    Massachusetts
    MS-Off Ver
    2007
    Posts
    30

    Re: 3s and 1s-Adding until the number changes

    [attach]WHERE DID I GO WRONG.xlsx[/attach]
    Attached Files Attached Files

  19. #19
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: 3s and 1s-Adding until the number changes

    Both versions in one. Drop this into C13, then fill the whole table with it.

    =IF(ISODD($A2),IF(C2=D2,0,SUMIF($C2:C2,C2,$C2:C2)+(C2=3)-SUMIF($B2:B2,C2,$B13:B13)+IF(C2=3,COUNTIFS($B2:B2,3,$B13:B13,">0"))),IF(C2=B2,0,SUMIF(C2:$ES2,C2,C2:$ES2)+(C2=3)-SUMIF(D2:$ET2,C2,D13:$ET13)+IF(C2=3,COUNTIFS(D2:$ET2,3,D13:$ET13,">0"))))

    The numbers in A13:A22 appear to be formatted as text, I ISODD($A13) wouldn't work without coercion. If you encounter circular references when you try to enter the formula into your sheet, then it is most likely the same problem.

+ 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. Adding a positive number with a negitave number to get less
    By momlamar in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-16-2017, 04:39 PM
  2. convert a negative number to a positive number by adding 49
    By khanaran in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-28-2016, 09:57 AM
  3. Replies: 1
    Last Post: 01-21-2016, 12:18 PM
  4. Cell not adding number and remembering last number added
    By taichi56 in forum Access Tables & Databases
    Replies: 4
    Last Post: 12-03-2015, 10:18 PM
  5. Counting the number of matched cells and adding it to a different number...
    By Hyflex in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-25-2015, 02:51 PM
  6. Adding 0 as prefix for number when number is quoted
    By VIJEXCEL in forum Excel General
    Replies: 3
    Last Post: 04-05-2011, 05:38 PM

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