+ Reply to Thread
Results 1 to 16 of 16

Formula for decreasing repetitions of numbers

  1. #1
    Registered User
    Join Date
    09-25-2019
    Location
    Salt Lake City, UT
    MS-Off Ver
    365
    Posts
    6

    Formula for decreasing repetitions of numbers

    I've spent the last 2 days trying to come up with a formula that will produce a list of decreasing numbers with each number being repeated a diminishing number of times.

    For example...

    7
    7
    7
    7
    7
    7
    7
    6
    6
    6
    6
    6
    6
    5
    5
    5
    5
    5
    4
    4
    4
    4
    3
    3
    3
    2
    2
    1

    The starting number (n) will vary, but the initial number of repetitions will always be n.

    This is the closest that I've come so far.

    =INT((ROWS($AM$38:AM38)-1)/($I$49-INT((ROWS($AM$38:AM38)-1)/$I$49)))

    Where I49 is the initial number (n). It works for the first set of repetitions, but fails quickly thereafter.

    Thanks in advance,

    Brent
    Last edited by DrBrentRD; 09-25-2019 at 01:32 PM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Formula for decreasing repetitions of numbers

    Can you explain the exact logic you are using for the number of repetitions? Your example sequence shows six 7s, five 6s, five(?) 5s, four 4s, three 3s, two 2s, and one 1. How do you decide when to shift from i-1 repetitions to i repetitions during the sequence?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  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: Formula for decreasing repetitions of numbers

    I assume your series of 7s and 6s is a typo since there are only 6 7s and 5 6s.

    With the seed value in A1 then in A2 copied down

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Obviously adjust for your actual seed value.
    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
    09-25-2019
    Location
    Salt Lake City, UT
    MS-Off Ver
    365
    Posts
    6

    Re: Formula for decreasing repetitions of numbers

    MrShorty,

    In looking at your question, I just noticed that my original example was flawed. It should be seven 7s, six 6s, five 5s etc. all the way down to 1. Thanks for catching that.

    Richard,

    Your equation will not work as it does not decrease the number of repetitions and will only alter the number on one row. The row that matches A1. Thus, in the example above, only row 7 will be changed to a 6 all other rows will be a 7.

    Thanks

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Formula for decreasing repetitions of numbers

    Have you actually tried Richard's formula? I used a very similar formula, and it seemed to work just fine.

  6. #6
    Registered User
    Join Date
    09-25-2019
    Location
    Salt Lake City, UT
    MS-Off Ver
    365
    Posts
    6

    Re: Formula for decreasing repetitions of numbers

    My mistake. I missed the lack of absolute references in the formula when I first looked at it. It's brilliantly simple.

    It's not quite what I need. It is one short of the seed value. In other words, it only generates 6 copies of 7 as it includes the seed value in the count for the first set. My seed value is elsewhere on the sheet. It's easy enough to work around this.

    My ultimate goal was to incorporate this calculation into another formula. Where this value would be used as the row number in an INDEX function. I'm not sure how to use this formula within the INDEX function without a helper column. It's a minor quibble, but really try to avoid helper columns.

    Thanks for both of your help.

  7. #7
    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: Formula for decreasing repetitions of numbers

    I'm not quite sure what you're getting at. Would you upload the workbook and add some notes to describe what you want to see and where.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula for decreasing repetitions of numbers

    Yes, post a sample sheet, showing the data you want to use INDEX-MATCH on, along with some manually calculated results.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  9. #9
    Registered User
    Join Date
    09-25-2019
    Location
    Salt Lake City, UT
    MS-Off Ver
    365
    Posts
    6

    Re: Formula for decreasing repetitions of numbers

    Here's a redacted sheet that should provide the needed detail. Hopefully, my notes therein are sufficient. Several of the formulas use functions from the Real Statistics Addin.
    Hopefully, that doesn't mess things up too much.

    Thanks again.
    Attached Files Attached Files

  10. #10
    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: Formula for decreasing repetitions of numbers

    Are you flagging up that your results starting in AI38 are not correct.

    Does changing AI38 From =G46-1 to just =G46 not correct that?

  11. #11
    Registered User
    Join Date
    09-25-2019
    Location
    Salt Lake City, UT
    MS-Off Ver
    365
    Posts
    6

    Re: Formula for decreasing repetitions of numbers

    AI38 should be 1 minus the total number of treatments, as there are only 6 comparisons to make with 7 treatments. What I'd like to do is find a way to incorporate the calculations in column AI into the
    formulas within column Y and do away with column AI altogether.

    To be frank, helper columns are a pet peeve of mine. Plus I find that when I pass these sheets off to colleagues they won't question anything within a formula, but I'll end up repeatedly explaining
    why I have a column of descending numbers that don't seem to fit in with anything else on the sheet. Ultimately, this is a minor quibble. The major calculations are all there. This is really just a housekeeping
    item for me.

    I appreciate you taking the time to assist me with my OCD.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula for decreasing repetitions of numbers

    f38:g45
    Y38, downwards

    I'd be tempted to use something like this... I have added new formulae in the ranges above. Different shading = different formulae. Any use??
    Attached Files Attached Files

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula for decreasing repetitions of numbers

    I've also amended Z38 & down. Check it carefully, as I'm not 100% sure I'm right....

    =IF(Y38="","",INDEX(G:G,AGGREGATE(15,6,ROW($G$38:$G$45)/($G$38:$G$45<>Y38),COUNTIF($Y$38:Y38,Y38))))
    Attached Files Attached Files

  14. #14
    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: Formula for decreasing repetitions of numbers

    Quote Originally Posted by DrBrentRD View Post
    To be frank, helper columns are a pet peeve of mine. Plus I find that when I pass these sheets off to colleagues they won't question anything within a formula, but I'll end up repeatedly explaining
    why I have a column of descending numbers that don't seem to fit in with anything else on the sheet. Ultimately, this is a minor quibble. The major calculations are all there. This is really just a housekeeping
    item for me.

    I appreciate you taking the time to assist me with my OCD.
    I think you probably have an answer now from Glenn's latest.

    Don't set your head against helper columns. In most cases where a straighforward function or combination of a functions in a composite formula don't help then helper columns are invaluable. They also make stuff easier to read and understand. If you don't want others to see them then just hide them and protect the workbook structure.

  15. #15
    Registered User
    Join Date
    09-25-2019
    Location
    Salt Lake City, UT
    MS-Off Ver
    365
    Posts
    6

    Re: Formula for decreasing repetitions of numbers

    Glenn,

    Not quite.

    There are a couple of the comparisons that are duplicated (e.g. Treatment 1-Treatment 2 is the same as Treatment 2-Treatment 1, in this case)
    It does not appear to be including the last treatment option in the group 1 (Y) column. In fact, treatment 5 is missing from most comparisons, only showing up in comparison with treatment 1.

    Your use of AGGREGATE is interesting. I keep forgetting about that little gem. It's a great way to avoid array formulas.

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula for decreasing repetitions of numbers

    Ah yes... This should be it:

    =IF(Y38="","",INDEX(G:G,AGGREGATE(15,6,ROW($G$38:$G$45)/(ISNA(MATCH($G$38:$G$45,$Y$38:Y38,0))),COUNTIF($Y$38:Y38,Y38))))

    You're welcome.



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

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Attached Files Attached Files

+ 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. Replies: 4
    Last Post: 04-25-2019, 06:59 AM
  2. Decreasing Numbers on a Specific Date
    By JFerro in forum Excel General
    Replies: 2
    Last Post: 12-10-2018, 01:49 PM
  3. Number of repetitions in a collumn and counting those repetitions
    By ctrc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2017, 11:36 AM
  4. [SOLVED] Creating Formula to identify repetitions of numbers
    By Rodh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-24-2012, 07:09 PM
  5. Decreasing numbers in cell
    By angelboy in forum Excel General
    Replies: 8
    Last Post: 01-23-2010, 10:32 AM
  6. Increaseing/Decreasing selected row of numbers without using a sum
    By nawtwrong in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 10-18-2007, 05:01 AM
  7. increasing/decreasing; decreasing rate - problem
    By vasyuta in forum Excel General
    Replies: 0
    Last Post: 08-23-2006, 05:26 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