• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Help to build a formula

gurinderwalia

New Member
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
 

Attachments

  • Book1.xlsx
    9.8 KB · Views: 14
I don't know what version of Excel you are using; I only code for 365.
Code:
= 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.
79048
 

Attachments

  • PercentileRanking.xlsx
    13.1 KB · Views: 4
Aren't we talking percentiles here?
You could try something in cell F2 of your file along the lines of:
Code:
=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.
 
Last edited:
gurinderwalia
Please, reread Forum Rules
There are clear sentences about Cross-posting.
 
Back
Top