Need Help to build a formula

[gurinderwalia]

I need to your help with a formula im trying to build and its been days I have tried my ways but I haven’t reached anywhere

I have attached a sheet which is sample file…below are the steps I take to update.

1. I filter the data by Month as I have 6 months data.
2. I filter it by unit as I have two unit
3. I have 8 different columns with 8 metrics.
4. 1st select the first data column and sort is desc to asc.
5. I count the total day on that column.
6. For eg if its 50 then I need the top 30. Mid 40 and bottom 30
7. I multiply 50*30% give me m top30 and bottom 30 which is 15 each
8. Remaining 20 as my mid40 percent
9. I have to do this manually and assign the name on E Column for the first 15 entries as T30 then go on to other 20 and type M40 and remaining as B30.
10. Now I have data in thousands and it’s a very tedious task to do this everytime manually.

Is tehre a way to create a formula which takes count and cover the cells I need to my to mid and bottow.

Sample is given for you to refer

 

[Peter Bartholomew]

I don't know what version of Excel you are using; I only code for 365.

= LET(
     rank,    COUNTIFS(Month,Month, Unit,Unit, Measure,"<"&Measure),
     count,   COUNTIFS(Month,Month, Unit,Unit),
     percent, rank/(count-1),
     IFS(percent<30%, "B30", percent>70%, "T30", TRUE, "M40")
  )

If you can use the formula, it does not require you to sort the data, either by Unit or date.

 

[p45cal]

Aren't we talking percentiles here?
You could try something in cell F2 of your file along the lines of:

=CHOOSE(MATCH(PERCENTRANK.EXC($D$2:$D$34,D2),{0,0.3,0.7}),"B30","M40","T30")

and copy down. There's no need to sort the data by column D, no need to count, but regarding the filtering of months and units; those can be incorporated into the formula but I'd like to know which version of Excel you're using (especially whether you have the FILTER function available to you) and to see a representative file with more than 1 month's data and more than 1 unit.

 

[p45cal]

…cross-posted.

 

[vletm]

gurinderwalia
Please, reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

There are clear sentences about Cross-posting.