Slope problems

**pelikaan** · 01-11-2013, 07:13 AM

Hi there!

Ive been working on a dataset, and I cant seem to get my slope function to work. Can anyone tell if I have made a typo, or ordered a wrong functions?

=SLOPE(VLOOKUP(I2,$HNS$2:$QAL$227,MATCH(A2,$HNT$1:$QAL$1,0)-261, FALSE): VLOOKUP(I2,$HNS$2:$QAL$227,MATCH(A2,$HNT$1:$QAL$1,0)-9, FALSE),IF($J2 = "France",VLOOKUP(4,'MSCI Transposed'!$D$1:$HLX$10,MATCH('Transposed + StockPrice'!$A2,'MSCI Transposed'!$E$1:$HLU$1,0)-261,FALSE),0):IF($J2 = "France",VLOOKUP(4,'MSCI Transposed'!$D$1:$HLX$10,MATCH('Transposed + StockPrice'!$A2,'MSCI Transposed'!$E$1:$HLU$1,0)-9,FALSE),0)

I'm becoming quite desperate, since the deadlines are approaching

.

Best regards,

Pelikaan

**twiggywales** · 01-11-2013, 07:14 AM

if you upload abook it might be more clear as to me that means nothing

**pelikaan** · 01-11-2013, 08:43 AM

Hi twiggywales,

Unfortunately I can't upload my book due to its size.

My problem is basically the following:

I want to calculate the Beta for a range of companies, and therefore I opted to use =SLOPE.

The first part of slope, as you may know, refers to the range of values noted for the Y-axis, and the second component refers to the X-axis.
A normal slope function could be used by selecting a range of values such as A2:A56, but I need the function to find different ranges for the various companies. These values I identified using the two first VLOOKUPs in the formula, composing the Y-axis component and then using the IF functions to create the X-axis.

All these four formulas work, but when i enter them int the slope function, I don't get a return.

Kind regards and thank you for your time and effort,

Pelikaan

**twiggywales** · 01-11-2013, 09:06 AM

can you not strip it down a bit and upload a sample?

**pelikaan** · 01-11-2013, 09:09 AM

The book currently consists out of various tabs linked to each other. Perhaps I could send it you via WeTransfer?

**MrShorty** · 01-11-2013, 11:21 AM

1st observation: This

VLOOKUP(I2,$HNS$2:$QAL$227,MATCH(A2,$HNT$1:$QAL$1,0)-261, FALSE): VLOOKUP(I2,$HNS$2:$QAL$227,MATCH(A2,$HNT$1:$QAL$1,0)-9, FALSE)

is not a valid reference. The VLOOKUP() function returns a value (123), not a reference (not $A$1). This pair of VLOOKUP()'s does not return something that the SLOPE() function can interpret as a reference (something that looks like A1:A10). You might look at the OFFSET() or the INDIRECT() functions which return actual references rather than values.

Sometimes I feel like the odd man out on a forum like this, where everyone is trying to make a single cell do everything. If this were me, I'd separate the problem into steps, have different parts/cells do the different steps of the problem. To break this problem down, it looks like:

1) You have a large dataset
2) You want to extract a subset of that dataset based on one or more criteria. IMO, this is the real hard part of a problem like this.
3) Then you want to calculate the slope of a straight line best fit of that subset.

So, my solution would look something like this:

1) Build a second table (in an adjacent area of the spreadsheet, probably) where the data for the regression are extracted. The help file for the SLOPE() function http://office.microsoft.com/en-us/ex...010342903.aspx indicates that SLOPE() ignores booleans, text, and other non-numeric data. So maybe a simple =IF() function would be enough =IF(criteria is true,xvalue,false) and =IF(criteria is true,yvalue,false)
2) Then you can simply use the SLOPE function on this new table as is without further modification.

Here's a very simple example of how this could be done.

**thecrell** · 01-11-2013, 12:14 PM

Hi Pelikaan,

I have made a formula for working out beta of an asset compared to the market/benchmark using the following formula:

=SLOPE(OFFSET('Portfolio NAV'!$A$10,1,24,MATCH(E3,'Portfolio NAV'!A:A,FALSE)-MATCH(Stats!D3,'Portfolio NAV'!A:A,FALSE)),OFFSET(Benchmarks!$A$5,1,8,MATCH(E3,Benchmarks!A:A,FALSE)-MATCH(D3,Benchmarks!A:A,FALSE)))

where E3 is the end date of the data, D3 is the start date of the data, 'Portfolio NAV'!A:A and Benchmarks!A:A is the list of dates. 'Portfolio NAV'!$A$10 is where my returns data begins (as a weighted average of a collection of 30 assets). Benchmarks!$A$5 is the start of my benchmark returns (i had to compare against 3 different benchmarks and so it didnt start right on the returns).

Hope you manage to translate that into your work.

All the best.