+ Reply to Thread
Results 1 to 1 of 1

Sort rows in column A and average the quartiles in column B

  1. #1
    Registered User
    Join Date
    08-22-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    3

    Sort rows in column A and average the quartiles in column B

    Hello, I'm new to the forum, I hope somebody can help me. I will try to be as descriptive as possible, if I need to give further info please let me know, I will answer straight away.

    Column A: Names of the companies (stocks)
    Column B: the one year return for that company
    Column C: P/E ratio of the company

    What I need to do is:

    1.- Sort all the companies from the lowest to the highest P/E ratio (column C)
    2.- Get the average and median of each of the quartiles, in other words, first take the top 25% of the companies in the list and do an average of their returns (column B) after do the same for each of the remaining quartiles.
    3.- Do t-tests and F-tests between for the diferences of mean and median between the 1st and 4th quartiles only.

    So basically I only need to obtain mean and median of each quartile and the p-values of the t-tests and the F-tests between the 1st and 4th quartile.

    I attached one example of the table I'm trying to get.

    Basically, I have doing it using sort, then counting how many companies are there each year to determine the boundaries for the quartiles and then obatining means and medians. Finally I'm doing the data analysis to get the p-values for differences of means and medians. The problem is that it is really taking a lot of time, I have been doing it this way for almost three weeks and I til have a lot of data to go.

    So I was wondering if there is a way to do it using macros or VBA. If it is possible please let me know.

    Thanks a lot in advance!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Average 3 Rows, Display in Column
    By i43 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-07-2011, 03:49 PM
  2. Average all rows but last in a column
    By XCRP83X in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-08-2010, 08:59 AM
  3. How to sort on column C, but not include any rows with an x in column L
    By Launchnet in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2010, 10:23 PM
  4. Average of a column excluding certain rows
    By visi in forum Excel General
    Replies: 8
    Last Post: 01-06-2009, 07:18 PM
  5. Sort Column, Delete Rows, Delete Column, Move Molumn, more inside?
    By motown in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-13-2008, 09:44 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1