+ Reply to Thread
Results 1 to 9 of 9

Dynamic bins & labels for frequency formula

  1. #1
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Dynamic bins & labels for frequency formula

    Hi there,

    I am looking for a solution I believe is fairly simple, however, I cannot get a wrap around how to approach the problem.

    As per the enclosed file, I need a dynamic range for my FREQUENCY formula (both the labels and bins must be dynamic) that adjust based on a simple dropdown.

    Basically, the user needs to be able to choose a level exposure from a drop down (in 10% increments), and ultimately the goal is to count how many users that have chosen each 10% interval, except for the chosen level, where I need to calculate the amount of user that have chosen that exact level. To do so, I correspondingly want the bins to increment by 10%, except in the case of the level chosen by the user, as per the below example.

    Note however, that the labels must also adjust dynamically, and must be based on the values of the bins (so, labels should be dependent on bins, not the other way around).

    Any ideas? Any help is appreciated, thanks.

    Problem.png

    Thanks,
    Phil
    Attached Files Attached Files

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

    Re: Dynamic bins & labels for frequency formula

    Using your sample file, one option:

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

    note: change your delimiter from , to ; as required {to suit locale}

    edit: above over complicated for default strings {as I was trying something else and didn't think to change it}

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by XLent; 04-08-2019 at 04:19 PM.

  3. #3
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Re: Dynamic bins & labels for frequency formula

    Perfect, Xlent, works smoothly, except it continues all the way to 100%, rather than stop at 80% (as I need it to do; preferably, I would like to be able to define an upper limit based on a separate cell, such as A4 where I would type "80%" as the maximum in this case, and the last row of the bin would then return "80% +").

    Additionally, I forgot to mention that the bins must be in decimal form (i.e. 0,10 rather than 10), but once I change this in the A7 formula, it seems to mess up the formula in B7.

    What modifications do I need to make to account for these two features?

    Best,
    Phil

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

    Re: Dynamic bins & labels for frequency formula

    Quote Originally Posted by Phil123456789 View Post
    Perfect, Xlent, works smoothly, except it continues all the way to 100%, rather than stop at 80%
    Yes, I did say copy to row 15, only, i.e. exclude your upper bin (seemingly fixed).

    Quote Originally Posted by Phil123456789
    Additionally, I forgot to mention that the bins must be in decimal form (i.e. 0,10 rather than 10), but once I change this in the A7 formula, it seems to mess up the formula in B7.
    Given above use the below but, as before, copy to row 15 and stop.

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

  5. #5
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Re: Dynamic bins & labels for frequency formula

    Great, works perfectly!

    Just noticed one last thing, however, I forgot to include in the original post (last thing, I promise! )

    Would there be any way to adjust the formula to also handle chosen levels of half increments (i.e. 5%), such as 25%? In this case, the bins should adjust to:

    Attachment 619448

    I realize this complicates things as such cases require an additional bin (compared to the 20% case), however, turns out it is a necessity after all.

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

    Re: Dynamic bins & labels for frequency formula

    I'm afraid the attachment link doesn't work, at least not for me.

  7. #7
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Re: Dynamic bins & labels for frequency formula

    My apologies, try this workbook instead.

    EDIT: Turns out the formulas need to be able to handle all chosen levels (i.e. also 23%, 24%, 48%, etc.).

    For a chosen level of 24%, the bins and labels should appear as in the enclosed workbook.
    Attached Files Attached Files

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

    Re: Dynamic bins & labels for frequency formula

    I have to say that I find the bin & labels a little inconsistent, logically speaking, however the below will reproduce your manually derived results.

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

    note:
    I have assumed the upper bin is to remain fixed at 80% + regardless of criteria - so if 20% there would be one fewer bin than 24%
    given above these formulae should be applied to all rows in your table (i.e. overwrites your previously hardwired values in final row)

  9. #9
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Re: Dynamic bins & labels for frequency formula

    Indeed, the upper bin should be fixed, so the above works perfectly.

    Thanks yet again!

+ 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. [SOLVED] Dynamic adjustment of quartiles of N items to populate N/4 bins
    By FivestarMac in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-14-2016, 10:43 AM
  2. Histograms with dynamic Bins
    By dustin.accord in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-01-2015, 11:10 PM
  3. [SOLVED] Dynamic chart ranges based on formula- Issue on labels
    By james444 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-27-2015, 12:12 AM
  4. Replies: 2
    Last Post: 04-05-2014, 06:46 PM
  5. Frequency with subtotal function formula for dynamic histogram
    By 5150 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-12-2013, 08:37 PM
  6. Replies: 10
    Last Post: 09-18-2012, 10:33 AM
  7. Frequency Bins
    By John Bates in forum Excel General
    Replies: 3
    Last Post: 05-13-2010, 11:14 AM

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