We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Excel Frequency function problem

markatatos
markatatos asked
on
Medium Priority
421 Views
Last Modified: 2008-02-26
I am trying to build a macro, that uses the Frequency function in Excel, and a table of predefined values. I want the function to give me the correct values in Excel. E.g.

If Cells A1 to A8 contain the data {1,1,2,2,2,2,2,4} and cells B1 to B4 contain the ranges the result would be:

Ranges   Result
1           2
2           5
3           0
4           1

How is this implemented in VBA as a macro???
Comment
Watch Question

Commented:
You can use an Excel function called COUNTIF. Syntax:- =COUNTIF(range,condition).
Using your example:

Cell C1 = "=COUNTIF(A1:A8,"=1")
Cell C2 = "=COUNTIF(A1:A8,"=2")

And so on and so on.


Using a vba macro, you could do the following:

Sub Add_Fequencies()
   dim count_range as string
   dim count_freq as integer
   dim plot_column as integer

   count_range = "A1:A8"
   count_freq = 4
   plot_column = 3

   for counter = 1 to count_freq
       cells(counter,plot_column-1).value = counter
       cells(counter, plot_column).formula = "=COUNTIF(" & _
count_range & ",""=" & counter & """)"
   next counter

End Sub

Author

Commented:
What I am actually looking for is a way of using the predefined FREQUENCY function and not a custom one.

I need a macro that will set the answer in the cells as I noted before. The function you sent me is restricted to 1 to 8 and I dont want that(neither restrict it to column A). Additionally I need to use the predefined Freq function.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
Why do you need a macro when the Frequency function on its own does what you want anyway. By what you said, you should reject Frazer's answer based on your principles (despite both our macros providing the results).

Author

Commented:
Thanx, although I have been searching a way to use the predefined FREQUENCY function, time does not allow me to wait for any more answers. Anyway, cartti deserves those points as well, but it's true that a function is a more elegant way of the two, for achieving the task...
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.