• 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.

Please recommend me a excel formula that choose a number according with his freq.

F

febausa

Member
Please recommend me a excel formula that choose a number (column H) according with his freq. (Colum I).

I attach the hand made example.


Thank you for your help,
 

Attachments

  • hand made example.xlsx
    10.4 KB · Views: 11
Try………..

1] Add a helper in Column J, J4 formula copy down :

=LOOKUP(I4,{0;1;1.4;1.8;2.2},{"E";"D";"C";"B";"A"})

2] A2, array formula (confirm enter with SHIFT+CTRL+ENTER) copy across and down :

=IFERROR(INDEX($H$4:$H$56,SMALL(IF($J$4:$J$56=CHAR(65+COLUMNS($A1:A1)-1),ROW(J$4:J$56)-ROW(J$4)+1),ROWS($1:1))),"")

Regards
Bosco
 

Attachments

  • FregGroup.xlsx
    13.5 KB · Views: 10
@bosco_yip has provided you a great solution.

If you wanted you can take a piece of his solution - the helper column - and use a Pivot Table to break it out - I've taken the liberty of adding the PT to his file for illustration.
 

Attachments

  • FregGroup - with PivotTable.xlsx
    23.4 KB · Views: 6
Hello friends,

It is also possible that, without helper column and non array,

=IFERROR(AGGREGATE(15,6,($H$4:$H$56)/(LOOKUP($I$4:$I$56,{0;1;1.4;1.8;2.2},{5;4;3;2;1})=COLUMN(A1)),ROW(F1)),"")

David
 

Attachments

  • hand made example-1.xlsx
    12.6 KB · Views: 11
AGGREGATE, introduced since Excel 2010 and unable to work in Excel 2007 and below.

Here is another non-helper option, which can work to all excel versions.

1] Array formula (enter with SHIFT+CTRL+ENTER), copy across and down :

=IFERROR(SMALL(IF(6-COLUMNS($A:A)=MATCH($I$4:$I$56,{0;1;1.4;1.8;2.2}),ROW($I$4:$I$56)-ROW($I$4)+1),ROWS($1:1)),"")

Or…..

2] Non-array formula, copy across and down :

=INDEX($H:$H,SMALL(INDEX((6-COLUMNS($A:A)<>MATCH($I$4:$I$56,{0;1;1.4;1.8;2.2}))/1%%+ROW($4:$56),0),ROWS($1:1)))&""

Regards
Bosco
 

Attachments

  • FregGroup1.xlsx
    15.4 KB · Views: 8
With Function MATCH,

=IFERROR(AGGREGATE(15,6,($H$4:$H$56)/(MATCH($I$4:$I$56,{0;1;1.4;1.8;2.2})=5-MOD(COLUMNS($A$1:E1),5)),ROW(F1)),"")

I think my first version with function LOOKUP, better .

Hi Bosco,
Take over a million rows,INDEX($H:$H, to accept the result of 53 rows,
Not effective at all..


Regards
David
 
Hello friends, thank you very much for your answers. I apologize for not properly ask.

Please recommend me a excel formula that choose (input data) a number (column H) according with his freq. (Colum I).


Output data: A),B),C),D),E) freq. group indicate in row 1, colunm A,B,C,D,E


I attach the handmade example.

attach file where I explain more properly the problem, again I apologize for the inconvenience.
 

Attachments

  • handmade example-update.xlsx
    10.6 KB · Views: 9
febausa said:
Hi David:

Excuse me for no explain the problem adequately.

I try it again, see the attach file.

Thank you,
Click to expand...

David

I have problem with Dropbox, I sent again the file without Dropbox. I expect receive well.
 

Attachments

  • handmade example-update-1.xlsx
    11.3 KB · Views: 6
bosco_yip said:
AGGREGATE, introduced since Excel 2010 and unable to work in Excel 2007 and below.

Here is another non-helper option, which can work to all excel versions.

1] Array formula (enter with SHIFT+CTRL+ENTER), copy across and down :

=IFERROR(SMALL(IF(6-COLUMNS($A:A)=MATCH($I$4:$I$56,{0;1;1.4;1.8;2.2}),ROW($I$4:$I$56)-ROW($I$4)+1),ROWS($1:1)),"")

Or…..

2] Non-array formula, copy across and down :

=INDEX($H:$H,SMALL(INDEX((6-COLUMNS($A:A)<>MATCH($I$4:$I$56,{0;1;1.4;1.8;2.2}))/1%%+ROW($4:$56),0),ROWS($1:1)))&""

Regards
Bosco
Click to expand...

Hi Bosco:

Thank you.very good job.
 
You must log in or register to reply here.
Back
Top