need help with a formula

gurinderwalia

New Member
Joined
May 18, 2022
Messages
2
Office Version
  1. 365
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

Book1.xlsx
ABCDEF
1MonthCenterUnitWork per hourMsg per hourswork per hour Group
2May-22MumbaiBC1005.45T30
3May-22MumbaiBC662.17T30
4May-22MumbaiBC640.31T30
5May-22MumbaiBC630.84T30
6May-22MumbaiBC5610.2T30
7May-22MumbaiBC550.41T30
8May-22MumbaiBC4612.37T30
9May-22MumbaiBC4210.7M40
10May-22MumbaiBC410.41M40
11May-22MumbaiBC366.14M40
12May-22MumbaiBC307.19M40
13May-22MumbaiBC303.75M40
14May-22MumbaiBC292.5M40
15May-22MumbaiBC289.5M40
16May-22MumbaiBC2712.04M40
17Apr-22MumbaiBC266.19M40
18Apr-22MumbaiBC2510.28M40
19Apr-22MumbaiBC238.77M40
20Apr-22MumbaiBC233.77M40
21Apr-22MumbaiBC223.36M40
22Apr-22MumbaiBC2012.26M40
23Apr-22MumbaiBC192.89M40
24Apr-22MumbaiBC183.55M40
25Apr-22MumbaiBC178.43M40
26Apr-22MumbaiBC147.19M40
27Apr-22MumbaiBC123.42M40
28Apr-22MumbaiBC115.31B30
29Apr-22MumbaiBC102.7B30
30Apr-22MumbaiBC911.28B30
31Apr-22MumbaiBC85.82B30
32Apr-22MumbaiBC63.48B30
33Apr-22MumbaiBC56.87B30
34Apr-22MumbaiBC19.85B30
Sheet1
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula to Count from Mid-Range to Bottom
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top