nonlinear regression

kc011c

New Member
Hi all. I've got two sets of data, (let's say, one series = \$ sales, the other is number of salespersons). Assuming a diminishing marginal return relationship (i.e. one more salesperson will bring in less in sales than the last salesperson did), I want to do a non-linear regression that will allow me to see what is the optimal number of salespeople (zero marginal returns). I can select "regression" from Data Analysis, but the summary statistics are all from a linear regression. I can only change the trendline in the chart to a polynomial regression. I need to know how to get the non-linear output as well as how to evaluate the regression model (do I still look at the R^2 value from the trendline?). Thanks

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hi there,
if you take logs of the data, then do a linear regression, it will be analogous to doing a non-linear regression (I think) with the coefficient of the log of the parameter being the power of the parameter. The R^2 can then be used as normal.

Hi there, thanks for the reply. But how can I see where the optimal number of salepeople is with a linear regression? With the curved trendline I can see where it peaks... and perhaps use solver to tell me where that point is...Appreciate any light you can shed on this matter.

Hello again,
the linear regression will give you the best fit line, ie if your best fit is:

log(sales)=2.5*log(people) then your formula that you need to max is sales = people^2.5

(not the best example because this doesn't max, but hopefully this helps)

Edit: Not very well explained - if you send me an email I can work you up a solution really quickly.
This message was edited by sann0638 on 2002-10-18 06:17

Replies
1
Views
742
Replies
3
Views
1K
Replies
0
Views
798
Replies
0
Views
1K
Replies
9
Views
267

1,203,170
Messages
6,053,866
Members
444,690
Latest member
itgldmrt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back