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

LOOPING THE FREQUENCY FUNCTION IN AN ARRAY

V

vicktor schausberger

Member
Hello developers.

I am looking how to looping the frequency function, in a range of 18 rows each time until the end of the array, and the bin is from 1 to 53.

My data is at B2:G133, in the file I loaded (as illustration); you will see the columns for the frequency, I was doing column by column, I mean {=FREQUENCY(B3:G21,H2:H54)}, {=FREQUENCY(B4:G22,H2:H54)}, {=FREQUENCY(B5:G23,H2:H54)} an so on to the end of the array; trying to do this in my real workbook?, I will spend weeks, because my data is B2:G2065; reason why I need your help gurus, to coding the frequency function all the way down, of course I understand the last 18 numbers will be impossible, because the code will not going to find the condition of 18 numbers, you know what I mean.
 

Attachments

  • 01CHANDOOLOOPFREQUENCY.xlsx
    16.6 KB · Views: 8
Hi Victor

I am a bit confused. This;

reason why I need your help gurus, to coding the frequency function all the way down
Click to expand...

All the way down to where exactly? Plain english and good examples - priceless!!! If you can provide a bit more clarity we might be able to assist. Personally I would like to see an example of a few end products when you attach a file.

Take Care

Smallman
 
How about in I2: =FREQUENCY(OFFSET($B$3:$G$20,COLUMN()-COLUMN($I$1),),H2:H54)
Array enter that into Column I
Then copy Column I accross
 
Smallman, thanks for reading my post, I agree, sorry I didn't make my point clear.
All the way down to where exactly?, good question,
ANSWER = my array is B2:G2065 ("sorry, I believe this means, start in the cell B2 and finish in cell G2065 or in other words from B to G there are 6 columns and 2065 rows "exactly the last row [B2065:G2065]); I upload just the minimum trying to make easy for the audience.

"Personally I would like to see an example of a few end products when you attach a file."; **** In the file I attached, there are 133 rows and six columns as illustration.

What I mean is that from B2 until G133 is an array. In column "H" the numbers from 1 to 53, as the bin to be used in the frequency function. The columns I,J,K,L,M (5 columns), are the results of the frequency functions, taken from the array. On top of every column is where the frequency is taken, in column "I" row one, says B3:G20 means {=frequency(B2:G20,H1:H54)}. I am really sorry this is not enough.

2065-18=2047 this is the amount of frequency function, I would like to avoid, by getting the code.

My apology I bother you.
 
thanks Hui and NARAYANK991. I really would like to see a code, first I am trying to teach vba to myself, and second since I start to read about excel and vba, I have the same question, why if you can program or code anything, why excel or the people behind this great ideas use formulas and functions, some times the formulas are longer than a code.
thank you Hui, long time ago you help me with your answers. (you really did).
thank you NARAYANK991 I love when you read my questions, you are great.
 
Vicktor,

Did my Formula solution above work ?

Formulas are native to Excel and highly optimised and so run very very fast

VBA sits on top of Excel and although it can run very fast when properly coded, most people don't know how to code optimally and so often there code is quite slow. On top of this is the overhead of extracting and returning the data to Excel.
 
Thanks Hui .
About your formula, I tried and..do not work. absolute reference do not apply here, because the frequency is for each row {=FREQUENCY(B3:G21,H2:H54)}, {=FREQUENCY(B4:G22,H2:H54)}, {=FREQUENCY(B5:G23,H2:H54)} as you see here B3:G21 results in column I, B4:G22 results in column J and so on.

as you see 21-3=18, 22-4=18 etc, that's the range I need to study. Thanks Hui . see you soon.
 
Hi Vicktor ,

Here is another workbook , using the FREQUENCY function !

If you use VBA , what the code will do is essentially use the same FREQUENCY function , unless you want code which does not use it.

Narayan
 

Attachments

  • 01CHANDOOLOOPFREQUENCY_1.xlsx
    63.3 KB · Views: 6
Vicktor

That is exactly what the offset function together with the Column functions in my formula does?
I had missed putting 2 $signs in the H column at the end

So try this:
=FREQUENCY(OFFSET($B$3:$G$20,COLUMN()-COLUMN($I$1),),$H2:$H54)

It offsets the range B3:G20 down by the Columns it is copied across
see attached
 

Attachments

  • 01CHANDOOLOOPFREQUENCY.xlsx
    18.7 KB · Views: 7
Thank you Hui. Tank you NARAYANK991. Both formulas work, show me the results in different way, I didn't think the way narayank991 did, good surprise. HUI you did exactly the way I need. Now I am facing the second part of the problem, but I don't know if I have to open new thread about it.

Any way I have to say it, and you will tell me about open new thread. is to swap the value of the frequency result with the original array and creat new list in different worksheet. thanks narayank991.


THANK YOU HUI...... see you soon.
 
You must log in or register to reply here.
Back
Top