Solved

Calculating decile medians...

Posted on 2006-06-30
6
1,952 Views
Last Modified: 2008-01-09
My spreadsheet currently has these three columns: CompanyID, FactorScore, StockReturn.

I need to create a second sheet that will calculate the median StockReturn for each decile of FactorScores. In other words, sort the factor scores and form into 10 equally-sized groups. Then calculate the median stock return within each decile group.  

            MedianStockReturn
Decile1    xxx
Decile2    xxx
Decile3    xxx
...
Decile10  xxx

The stock returns are live, so I'm looking for a solution that is continually recalculating.

Thansk!

0
Comment
Question by:tomfolinsbee
  • 3
  • 3
6 Comments
 

Author Comment

by:tomfolinsbee
ID: 17016231
I added a column called Percent Rank to the first sheet.
And I added two columns to the 2nd sheet: StartRank and EndRank

             StartRank    EndRank      MedianStockReturn
Decile1      0%          10%           xxx
Decile2      10%         20%           xxx
Decile3      20%         30%           xxx
...
Decile10    90%        100%           xxx


So now I'm trying to calculate the median stock returns like this:

if PercentRank between StartRank and EndRank then calculate the median stock return

0
 

Author Comment

by:tomfolinsbee
ID: 17016265
something like this:

=if(ranks!D:D>=deciles!B3 and ranks!D:D<deciles!C3, median(ranks!G:G)

ranks = first sheet, D:D = PctRank, G:G= StockReturns
deciles = 2nd sheet, B = startrank, c=endrank
0
 
LVL 6

Expert Comment

by:garlin007
ID: 17025895
Sounds like you could solve this using a PivotTable.

You would use the grouping function to group the ranks into 10s, and then calculate the median for each group.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:tomfolinsbee
ID: 17034517
Pivot table doesn;t seem to calculate median, only average, sum, min, max etc
0
 
LVL 6

Accepted Solution

by:
garlin007 earned 500 total points
ID: 17035061
Right you are. I have solved the problem, but the solution is difficult to describe. I will put the highlights here, and have posted a working example to www.ee-stuff.com at
https://filedb.experts-exchange.com/incoming/ee-stuff/327-Decile-Median.zip

I created a test worksheet with a Database Named Range R6C2:R36C4 with 3 columns and 30 rows of test data. The columns are

Factor Score    Stock Return    Decile    In Decile

I ignore the Company ID, since it is not being used, but I left room for it in Column 1. The Decile column contains a number from 0 to 9 indicating the decile that Stock Return belongs to. The "In Decile" to the right of the table in column 5 is not part of the Database, but I show it since I will use it to extract a portion of the Factor Scores one decile at a time.

I name the range R7C5:R36C5 InDecile. This is the InDecile column excluding the row title.

I created a named range in R2C4 called DecileCritera. It will contain a number from 0 to 9, representing the decile I want to extract into "In Decile". I put a formula in this cell

=ColumnInputCell

which refers to the Column Input Cell in a Data Table that we will create later.

In R7C4, which is in the Decile column to the right of the first row of data, I put the formula

=INT(10*RANK(RC[-1],Database R7C3:R65536C3,1)/(ROWS(Database)-1))

This formula first isolates the intersection between Database and R7C3:R65536C3, which is essentially the data in the Stock Return column. The RANK function returns a number between 1 and the number of data rows in the database, indicating the rank of the Stock Return in the list. We divide by the number of data rows (we have to subtract 1 since the Database range includes the column titles), multiply by 10 and drop the fractional part. What remains is an integer from 0 to 9 indicating the decile that the Stock Return belongs to.

In R7C5, which is in the In Decile column to the right of the first row of data, I put the formula

=IF(RC[-1]=DecileCriteria,RC[-3],"")

This formula compares the number in the Decile column to DecileCriteria. If it is a match, then we extract the Factor Score. If it isn't a match, we put in a blank so it will be ignored by the Median formula.


We will create a Data Table in the range R40C7:R50C8. Above this range, in R39C7:R39C8 I put two column titles

Decile    Decile Median

Then in R41C7:R50C7 I put the numbers 0 to 9. These label the 10 results that will be in R41C8:R50C8, which is the deciles you are looking for.

I name the range R40C7 ColumnInputCell. If you recall, this was the name referred to in the formula in the DecileCriteria.

In cell R40C8 I put the formula

=MEDIAN(InDecile)

This returns the Median of the values in the In Decile column. That column, though, only has values for one decile at a time, depending on what is entered in the ColumnInputCell. Since this cell is blank, the 0 decile is calculated.

Highlight the range R40C7:R50C8. Use Data, Table, and enter ColumnInputCell for the Column Input Cell. Voila, the 10 Medians are calculated in the Decile Median column.

0
 
LVL 6

Expert Comment

by:garlin007
ID: 17035080
Forgot to mention that after creating the formulas in R7C4:R7C5, you copy them down to row 36, which is the end of the data table.

Also forgot to mention that you need to set Excel to R1C1 reference style (Tools, Options, General) in order to enter the formulas.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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 simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now