+ Reply to Thread
Results 1 to 7 of 7

auto-compensate proportions based on another cell's value

  1. #1
    Registered User
    Join Date
    06-24-2020
    Location
    Ireland
    MS-Off Ver
    Office for Mac
    Posts
    3

    auto-compensate proportions based on another cell's value

    Hi All - am pulling my hair out to try and figure out how to do this - any help greatly appreciated!

    If you have 5 amounts, that all sum to 100%, say:

    Amt A: 20%
    Amt B: 5%
    Amt C: 18%
    Amt D: 21%
    Amt E: 36%
    ---------------
    Total: 100%

    How can I make things so that if I change any of those amounts, the others automatically compensate proportionally to keep the total at 100%?
    Example: Change Amt C from 18% to 28%
    So now the total is 110%, and I want the extra 10% to be re-distributed proportionally among the other Amts.

    Can anyone see an elegant way to do this?
    Last edited by AliGW; 07-03-2020 at 05:35 AM. Reason: Irrelevant section of title removed: this is a HELP forum!!!

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,851

    Re: auto-compensate proportions based on another cell's value

    Realistically you would have to use an Event (VBA)

    The below would sit in the sheet object in which the range of interest resides (right click tab name, view code and paste below into resulting window)

    For sake of illustration below assumes Amounts are entered in B1:B5
    when any one amount is modified the others will adjust, apportioning the movement relative to their respective weighting {exclusive of amount that is being changed}

    So, in this scenario, A would be adjusted to 18%, B to 4%, D to 18% and E to 32%
    (i.e. E takes 44% of the -10% adjustment as old value of 36% equates to 44% of the old 82% aggregate)

    Please Login or Register  to view this content.
    Last edited by XLent; 07-03-2020 at 07:24 AM. Reason: forgot declarations!

  3. #3
    Registered User
    Join Date
    06-24-2020
    Location
    Ireland
    MS-Off Ver
    Office for Mac
    Posts
    3

    Re: auto-compensate proportions based on another cell's value

    Thank you so much, this is fantastically helpful and lets me keep some hair on my head. I have a couple of questions as I am definitely not as XLent with code as you are!
    1. For the total-to-remain-unchanged, in this case 100%, how can I adjust what I want that total to be? For example, 85% or 12 units or whatever?
    2. Is there any way to re-set or undo? I notice that once it's running I cannot undo/step-back, or re-set the numbers to their original settings.
    3. How could I set it so that it checks different non-contiguous ranges, e.g. B1:B5, C12:C15 etc., but makes sure that all of them summed keep to the total as set?


    Many thanks for your generosity of expertise

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,851

    Re: auto-compensate proportions based on another cell's value

    Quite a few elements to the post above; I suspect a file outlining a few of the various scenarios with some expected results (i.e. before / after) would help

    Resetting, and undoing etc is easy enough however, depending on how flexible you want this to be you may choose to use a (very hidden) audit log (tab) rather than Statics etc.

    In summary - once you start to use VBA things get relatively flexibile
    for ex. you could establish a prefix which the Event checks for, (and removes), but ensures you circumvent the apportionment logic.

    In terms of checking non-contiguous ranges -- presumably you don't mean in parallel? If you do, then the use of the Change Event may not be viable - we'd need to understand how the precedents, impacting those non-contiguous ranges, are being updated. Whilst you can, obviously, update non contiguous cells manually in parallel, c/o SHIFT + ENTER, it would be a little unusual in this type of context.

  5. #5
    Registered User
    Join Date
    06-24-2020
    Location
    Ireland
    MS-Off Ver
    Office for Mac
    Posts
    3

    Re: auto-compensate proportions based on another cell's value

    Thank you, and fair enough - so I think the main thing for me to understand if you could help, would be to know how can I set the 'total' to be something other than 100%?

    I'm really a newbie with VBA so please assume my ignorance!

    Non-contig. ranges: what I mean here I think is in sequence not parallel, and all the cells would be in the same column. So the event would still be a single change to a single cell in that column, but there may be spaces to ignore between the cell ranges, so the cells to be checked sequentially would be B1:B12 then B15:B17 etc.
    I tried to specify that using:
    Set rngInterest = Range("B1:B12, B15:B17")
    but the total seemed to go over 100% in certain circumstances, so I just wanted to check if this was the right way to do it.

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,851

    Re: auto-compensate proportions based on another cell's value

    But, where do you envisage entering the revised total (i.e. was 100% now 85%) whilst, at same time, entering the revised % (28%) ?

    I suspect you may end up wanting / needing to change tack altogether
    e.g. switching from Change Event to the BeforeDoubleClick Event on these ranges, such that user is prompted to enter new total, and new % via Input Box dialogs.

    In relation to the non-contiguous ranges question -- if there are true blanks between these ranges, such that they are discrete areas, you may find you can get away with:

    - verifying initial intersect (as you did)
    - thereafter relying on the CurrentRegion of the Target {and intersect with B)

    but, without seeing the setup it's hard to give definitive answers.

    I'm afraid I am heading offline but there are others who will be more than happy to assist, I'm sure, if you can provide feedback re: above.

  7. #7
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,851

    Re: auto-compensate proportions based on another cell's value

    Hi, I've pulled together the attached which utilises

    1. the BeforeDoubleClick Event {to assist with 'total' question}
    2. a (very hidden) Log sheet {to assist with 'resetting' a given region at any point to values as per session start}

    What you should find is that you can, via double click on any given yellow cell, initiate the new target / new value entry - and subsequent recalc across remainder of region

    if you click Cancel on the first dialog you can confirm a reset -- this will leverage the (very) hidden tab which cached the values when the code first ran, in that session
    (use of the alternative Static is more complex given the multiple regions)

    as noted, the regions can be modified / reset independent of one another, i.e. you can change B10:B12 without impacting B1:B5 and vice-versa.

    There are invariably going to be more elegant / succinct approaches but this is what first came to mind and, hopefully, helps you work through the practicalities of what you're trying to achieve if nothing else.

    Please Login or Register  to view this content.
    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. vba that can compensate my vlookup work
    By ROHAN999 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-12-2018, 01:39 PM
  2. [SOLVED] Oops I formatted my data in a really unhelpful way - can you help me compensate?
    By jlt199 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-05-2018, 11:54 AM
  3. [SOLVED] Auto Fill Row based on value and auto delete shift cell up based on value too
    By morr1 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 03-14-2017, 07:12 PM
  4. [SOLVED] How to compensate for a second shift spread of data across two dates
    By Jay S. in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-04-2015, 08:51 PM
  5. [SOLVED] calculate which proportions of a debt should be paid based on two different incomes
    By Marvlin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-25-2014, 04:18 PM
  6. [SOLVED] Auto Populate cell based on auto filter selection from table in same sheet
    By missydanni in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-02-2013, 05:03 PM
  7. Replies: 6
    Last Post: 06-19-2009, 09:17 AM

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