need help with 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

Book1.xlsx
	A	B	C	D	E	F
1	Month	Center	Unit	Work per hour	Msg per hours	work per hour Group
2	May-22	Mumbai	BC	100	5.45	T30
3	May-22	Mumbai	BC	66	2.17	T30
4	May-22	Mumbai	BC	64	0.31	T30
5	May-22	Mumbai	BC	63	0.84	T30
6	May-22	Mumbai	BC	56	10.2	T30
7	May-22	Mumbai	BC	55	0.41	T30
8	May-22	Mumbai	BC	46	12.37	T30
9	May-22	Mumbai	BC	42	10.7	M40
10	May-22	Mumbai	BC	41	0.41	M40
11	May-22	Mumbai	BC	36	6.14	M40
12	May-22	Mumbai	BC	30	7.19	M40
13	May-22	Mumbai	BC	30	3.75	M40
14	May-22	Mumbai	BC	29	2.5	M40
15	May-22	Mumbai	BC	28	9.5	M40
16	May-22	Mumbai	BC	27	12.04	M40
17	Apr-22	Mumbai	BC	26	6.19	M40
18	Apr-22	Mumbai	BC	25	10.28	M40
19	Apr-22	Mumbai	BC	23	8.77	M40
20	Apr-22	Mumbai	BC	23	3.77	M40
21	Apr-22	Mumbai	BC	22	3.36	M40
22	Apr-22	Mumbai	BC	20	12.26	M40
23	Apr-22	Mumbai	BC	19	2.89	M40
24	Apr-22	Mumbai	BC	18	3.55	M40
25	Apr-22	Mumbai	BC	17	8.43	M40
26	Apr-22	Mumbai	BC	14	7.19	M40
27	Apr-22	Mumbai	BC	12	3.42	M40
28	Apr-22	Mumbai	BC	11	5.31	B30
29	Apr-22	Mumbai	BC	10	2.7	B30
30	Apr-22	Mumbai	BC	9	11.28	B30
31	Apr-22	Mumbai	BC	8	5.82	B30
32	Apr-22	Mumbai	BC	6	3.48	B30
33	Apr-22	Mumbai	BC	5	6.87	B30
34	Apr-22	Mumbai	BC	1	9.85	B30
Sheet1

 

[Fluff]

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.

 

[gurinderwalia]

this all new to me

 

[Joe4]

this all new to me

Not a problem. As we said, it is allowed, as long as you do it the proper way.
So be sure to read all the rules carefully.

And be sure to read this link for the reasoning/issue with Cross-Posting: Excelguru Help Site - A message to forum cross posters
I think once you read that, it will all make sense.