Let us suppose we have following data

x   y
1   5
2   4
3   5
4   4
5   7

I would like to do Quantile regression in excel, I have found following information about given method

enter image description here

based on this information let choose $q=75$ or 75% quantile, in excel I have done following structure, first create dummy variable based on indicator function and also choose arbitrary values of alpha and beta $$\alpha=0.1 $$
$$\beta=0.2 $$ dummy variable has been filled using following method

enter image description here

initially all values are zero, now I have calculated one column for sum

enter image description here

and finally I have calculated sum of all those values, and then using solver I have estimated coefficients which minimizes sum, I have got following values and result

enter image description here

please tell me if I am wrong, and also statistically could you explain me please what does this mean? What does those coefficient describe in terms of quantile?


(A little bit more a long comment than an answer, but I'm missing the repetition to comment)

First, your calculation of the loss appears to be correct (this is R code):

y <- c(5, 4, 5, 4, 7)
x <- c(1, 2, 3, 4, 5)
a <- 0.217092
b <- 1.594303
tau <- 0.75
f <- function(par, y, x, tau) {
    sum((tau - (y  <= par[1] + par[2]*x)) * (y - (par[1] + par[2]*x)))  
f(par=c(a, b), y=y, x=x, tau=tau)
[1] 3.782908

Second, there seems to be a problem with the Excel solver. Using R's optimizer, we find:

optim(c(0.1, 0.2), f, y=y, x=x, tau=tau)
[1] 4.4999998 0.4999998

[1] 1.250001

function gradient 
 143       NA 

so the loss is lower using optim than using Excel's solver.

Third, note that your approach of estimating quantile regression is inferior to solving the corresponding linear program. Anyway, a comparison with Roger Koenker's quantregpackage yields:

rq(y ~ x, tau=tau)
rq(formula = y ~ x, tau = tau)

(Intercept)           x 
        4.5         0.5 

Degrees of freedom: 5 total; 3 residual

which is very close to the solution of R's optim solver.

About your other question: could you elaborate what exactly you want to understand?

  • $\begingroup$ thanks for reply, @BayerSe so my approach for solving Qantile regression is correct right? in shortly what we estimated and what is the meaning of those coefficients? $\endgroup$ Oct 26 '17 at 18:05
  • 1
    $\begingroup$ @datodatuashvili Yes, your general approach is sound (but there is some problem with Excels' solver). You here estimate the parameters of a linear quantile regression model where you model the 75% quantile of the conditional distribution of your dependent variable. Your question about the meaning of the coefficients is unclear - meaning in what sense? $\endgroup$
    – BayerSe
    Oct 26 '17 at 18:09
  • $\begingroup$ their role, ok clear thanks in advance, so does it means that excel solver is unstable while we are trying to minimize L1 norms? or sum of absolute deviations? $\endgroup$ Oct 26 '17 at 18:11
  • 1
    $\begingroup$ Did you play around with the solver options? According to support.office.com/en-us/article/… there should be three different solvers. Maybe one of them works better. But generally, I would avoid doing stuff like that in Excel ... $\endgroup$
    – BayerSe
    Oct 26 '17 at 18:16
  • 1
    $\begingroup$ I see. Check out Koenker's website, he has some basic Matlab code there: econ.uiuc.edu/~roger/research/rq/rq.html. But generally, R or Python are better supported by the community than Matlab for quantile regression. $\endgroup$
    – BayerSe
    Oct 26 '17 at 18:27

solving solution using matlab, on the base of @BayerSe (thanks my friend,i always respect humans who share their knowledge to others), i have solved this problem in matlab

  1. define objective function

    f=@(a) sum((q-(y<=a(1)+a(2)x)).(y-a(1)-a(2)*x))

make initial guess of $\alpha$ and $\beta$ and $q$

a_b = [0.1,0.2];


and finally solve

options = optimset('PlotFcns',@optimplotfval);
x = fminsearch(f,a_b,options)

x =

    4.5000    0.5000

enter image description here


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.