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

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

Originally Posted by Phil123456789

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).

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:

Please Login or Register  to view this content.

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.

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

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.

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:

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)

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

Thanks yet again!