Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Frequency function problem

Posted on 1998-12-04
5
Medium Priority
?
373 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???
0
Comment
Question by:markatatos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 2

Expert Comment

by:cartti
ID: 1614840
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
0
 

Author Comment

by:markatatos
ID: 1614841
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.
0
 
LVL 3

Accepted Solution

by:
frazer earned 150 total points
ID: 1614842
Hi,

Enter this code into a module.....

Function MyFrequency(TheRange As Range, TheValue As String) As Long
    Dim a As Range
    Dim Counter As Long
    Counter = 0
    For Each a In TheRange
        If a.Text = TheValue Then
            Counter = Counter + 1
        End If
    Next
    MyFrequency = Counter
End Function


then put the one of the following functions into your spreadsheet...

=MyFrequency(A1:A8,A1)

or

=MyFrequency(A1:A8,"4")

The first variable must be a range, the second can be either a range or a string.

Hope this helps

Frazer
0
 
LVL 2

Expert Comment

by:cartti
ID: 1614843
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).
0
 

Author Comment

by:markatatos
ID: 1614844
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...
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question