Need help to build a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3385636%22%20slang%3D%22en-US%22%3ENeed%20help%20to%20build%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3385636%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%206%20months%20data.%3C%2FP%3E%3CP%3EI%20have%208%20different%20columns%20with%20different%20metrics.%3C%2FP%3E%3CP%3EI%20first%20filter%20the%20month%20and%20select%20past%20month.%3C%2FP%3E%3CP%3EI%20go%20to%20my%20first%20column%20of%20data.%3C%2FP%3E%3CP%3EI%20sort%20it%20desc%20to%20asc.%3C%2FP%3E%3CP%3EThen%20i%20count%20the%20data%20i%20have%20for%20that%20particular%20metric.%20For%20example.%2050.%3C%2FP%3E%3CP%3EI%20multiply%2050%20with%2030%25%20so%20that%20i%20get%20the%20number%20to%20assign%20top%2030%20and%20bottom%2030%20and%20remaining%20as%20mid%2040.%3C%2FP%3E%3CP%3EBut%20i%20have%20to%20do%20that%20manually..do%20the%20math%20count%20and%20type%20that%20on%20a%20new%20column%20like%20t30%2Cm40%20and%20b30.%3C%2FP%3E%3CP%3EI%20want%20to%20figure%20out%20that%20is%20there%20a%20way%20to%20create%20a%20formule%20where%20it%20sorta%20count%20multiple%20and%20assign%20t30%20to%20those%20top%20numbers%20and%20then%20move%20to%20m40%20and%20rest%20b30.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3385636%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3386958%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20build%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3386958%22%20slang%3D%22en-US%22%3EI%20think%20you%20want%20to%20have%20a%20couple%20helper%20cells%20somewhere%20with%20formulas%20something%20like%3A%3CBR%20%2F%3E%3DLET(d%2C%20FILTER(dataTable%2C%20MONTH(dateCol)%3D4%2C%20%22%22)%2C%20LARGE(%20d%2C%20INT(0.3*ROWS(d))%20))%3CBR%20%2F%3ESo%20basically%20filtering%20your%20data%20table%20based%20on%20the%20month%20(and%20you%20can%20add%20other%20conditions%20as%20needed.%20Then%20return%20the%20nth%20LARGEst%20value%20where%20nth%20is%2030%25%20time%20the%20number%20of%20rows%20found%20by%20the%20filter.%3CBR%20%2F%3EThen%20do%20the%20same%20with%20SMALL%3CBR%20%2F%3ENow%20that%20you%20have%20the%20critical%20points%20you%20can%20just%20use%20an%20IF%20or%20IFS%20statement%20to%20determine%20if%20that%20row%20matches%20the%20month%20and%20is%20%26gt%3BLARGE%2C%20%3CSMALL%20or%3D%22%22%20in%3D%22%22%20between.%3D%22%22%3E%3CBR%20%2F%3EIf%20you%20provide%20a%20sheet%20it%20would%20be%20easier%20to%20demonstrate%20it.%20If%20you%20don't%20have%20Excel%20365%20it%20can%20still%20be%20done%20but%20a%20little%20harder.%3C%2FSMALL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3387911%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20build%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3387911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3Bthanks%20for%20reply...trust%20me..i%20didnot%20understand%20the%20formula%20u%20gave...%3C%2FP%3E%3CP%3EU%20r%20right%20an%20excel%20sheet%20might%20help%20here%20if%20i%20can%20share%20that%20with%26nbsp%3B%3C%2FP%3E%3CP%3EGive%20me%20ur%20email%20address%20if%20that's%20ok%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3389965%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20build%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3389965%22%20slang%3D%22en-US%22%3Eif%20you%20click%20on%20my%20name%20you%20should%20go%20to%20my%20profile%20page%20and%20then%20click%20'Message'.%20You%20should%20be%20able%20to%20attach%20it%20there.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3395871%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20build%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3395871%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1392330%22%20target%3D%22_blank%22%3E%40Gurinderwalia%3C%2FA%3E%26nbsp%3BSee%20the%20attached%20sheet.%26nbsp%3B%20i%20added%20a%20formula%20column.%26nbsp%3B%20The%20problem%20I%20have%20is%20that%20you%20say%20'first%20I%20will%20filter%20to%20this%20month'%20but%20in%20the%20example%20you%20have%20both%20April%20and%20May.%26nbsp%3B%20The%20included%20formula%20uses%20a%20month%20filter%20so%20it%20doesn't%20line%20up%20with%20your%20answers%20but%20could%20easily%20be%20adjusted%20if%20you%20have%20a%20better%20description%20of%20how%20you%20filter%20(e.g.%20from%2020th%20of%20last%20month%20through%2019th%20of%20this%20month).%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have 6 months data.

I have 8 different columns with different metrics.

I first filter the month and select past month.

I go to my first column of data.

I sort it desc to asc.

Then i count the data i have for that particular metric. For example. 50.

I multiply 50 with 30% so that i get the number to assign top 30 and bottom 30 and remaining as mid 40.

But i have to do that manually..do the math count and type that on a new column like t30,m40 and b30.

I want to figure out that is there a way to create a formule where it sorta count multiple and assign t30 to those top numbers and then move to m40 and rest b30.

4 Replies
I think you want to have a couple helper cells somewhere with formulas something like:
=LET(d, FILTER(dataTable, MONTH(dateCol)=4, ""), LARGE( d, INT(0.3*ROWS(d)) ))
So basically filtering your data table based on the month (and you can add other conditions as needed. Then return the nth LARGEst value where nth is 30% time the number of rows found by the filter.
Then do the same with SMALL
Now that you have the critical points you can just use an IF or IFS statement to determine if that row matches the month and is >LARGE, <SMALL or in between.
If you provide a sheet it would be easier to demonstrate it. If you don't have Excel 365 it can still be done but a little harder.

@mtarler thanks for reply...trust me..i didnot understand the formula u gave...

U r right an excel sheet might help here if i can share that with 

Give me ur email address if that's ok

if you click on my name you should go to my profile page and then click 'Message'. You should be able to attach it there.

@Gurinderwalia See the attached sheet.  i added a formula column.  The problem I have is that you say 'first I will filter to this month' but in the example you have both April and May.  The included formula uses a month filter so it doesn't line up with your answers but could easily be adjusted if you have a better description of how you filter (e.g. from 20th of last month through 19th of this month).