Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 408K Members
- 8,949 Online
- 464K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- How to set the coefficient b for a power function

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-18-2019 06:42 PM

Hello,

I am wondering: does anyone know a way to manually set the coefficient b for a power function best fit line instead of it automatically setting itself for a set of data? I am specifically talking about a function in the form of y = a*x^b. I have a set of data where I already know the relationship between the two quantities, so I already know what the power b is. I need to fit the function such that it is a best fit in terms of the power b that I know, not what Excel automatically tries to set b as when it calculates best fit. Any help would be greatly appreciated. Thanks.

3 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-18-2019 08:48 PM

I have attached a spreadsheet that solves the problem by using Excel Solver function and minimizing the error sum of squares.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-18-2019 09:47 PM - edited 03-18-2019 10:00 PM

Hmm...that's significantly more complicated than what I was thinking of... I was talking more about if there was a feature for the power trendline that lets you change the value of b to what you want it to be, then adjusts the value of a and chi-square accordingly. Is there something like that or can it only be done using data manipulation? I figure it's Excel, so there has to be an easier way than that...

Also yes, you are correct in your assumptions that it would need to be a least-squares fit.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-21-2019 12:39 PM

* (Not sure this posted, second attempt, sorry if it is redundant*)

Hello:

There is no methodology that I know of that makes statistical sense. Of course you can do anything you want but that is quite different from following statistical protocol. Thus there is no Excel function that does that, nor should there be. What I suggested made no statistical sense but would solve the problem you ask about. There are probably a million ways to make an intercept once you stop being concerned about statistical protocol. Once you modify the output from a statistical procedure none of the statistical mean a thing. They are based on the function being estimated by following a set procedures.

Also I don't why you would use chi-square for anything associated with regression and correlation perhaps you meant R-squared which would have no meaning once you changed the "b" to some arbitrary amount. Since you "know the correct b" why not just solve for "a" by: a=Y/(X^b). Just choose any pair of Y & X. Since you know "b" it should produce the correct "a."

Related Conversations

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
21.7K
Views

1 Likes

35 Replies

flashing a white screen while open new tab

cntvertex
in
Discussions
on
10-05-2019
20.9K
Views

9 Likes

11 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
132K
Views

6 Likes

28 Replies

What is Canary ring in Windows insider program? and how do we get them?

HotCakeX
in
Windows Insider Program
on
09-27-2019
10.8K
Views

0 Likes

9 Replies

How to download windows server 2019 update to 1903

Cmakar37
in
Windows Server for IT Pro
on
07-03-2019
21.7K
Views

0 Likes

7 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft