Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


Upload your resume securely at https://www.dwsimpson.com
to be contacted when our jobs meet your skills and objectives.


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 01-30-2019, 05:34 PM
Cavaliere Cavaliere is offline
Member
SOA
 
Join Date: May 2016
Posts: 295
Default Excel Question

Hi All,

I'm sure there's a simple answer, but i don't know it and i'm dumb.

I have 2 columns: column A is a number, and column B is a percentage. Column B is dependent on column A. B tends to get bigger when A gets smaller, and vice versa. You are given historical values for A and B. What formula/function could i use to predict the value of B given A. I was thinking of using a regression, but i feel like that isn't right.

Example, Solve for x: (I originally had a space between the 2 columns, but this site formatted it by removing it. I put an underscore instead)

Column A______ Column B
2176.45 ______ 75.56%
1097.8 ______ 80.69%
2036.03 ______ 76.36%
2214.75 ______ 75.36%
2004.12 ______ 76.56%
1984.97 ______ 76.68%
2521.15 ______ 74.18%
2233.90 ______ x%
Reply With Quote
  #2  
Old 01-30-2019, 05:38 PM
Dr T Non-Fan Dr T Non-Fan is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 94,315
Default

Cubic Splines?

Excel can apply a number of different regressions: linear, exponential, quadratic, etc.

Would help to know what, in general, the dependence is based on.
__________________
"Facebook is a toilet." -- LWTwJO
Reply With Quote
  #3  
Old 01-30-2019, 05:43 PM
Childish Gambino's Avatar
Childish Gambino Childish Gambino is offline
Member
SOA
 
Join Date: Jul 2014
Posts: 27,111
Default

That's not an excel question. There are numerous models one could apply. You have to decide that and then if you don't know how to code your preferred model, we could help you with that.
__________________
the president replied, "I don't care. I believe Putin."
Reply With Quote
  #4  
Old 02-01-2019, 04:40 PM
DeepPurple's Avatar
DeepPurple DeepPurple is offline
Member
 
Join Date: Jun 2004
Posts: 4,266
Default

Quote:
Originally Posted by Cavaliere View Post
Hi All,

I'm sure there's a simple answer, but i don't know it and i'm dumb.

I have 2 columns: column A is a number, and column B is a percentage. Column B is dependent on column A. B tends to get bigger when A gets smaller, and vice versa. You are given historical values for A and B. What formula/function could i use to predict the value of B given A. I was thinking of using a regression, but i feel like that isn't right.

Example, Solve for x: (I originally had a space between the 2 columns, but this site formatted it by removing it. I put an underscore instead)

Column A______ Column B
2176.45 ______ 75.56%
1097.8 ______ 80.69%
2036.03 ______ 76.36%
2214.75 ______ 75.36%
2004.12 ______ 76.56%
1984.97 ______ 76.68%
2521.15 ______ 74.18%
2233.90 ______ x%
This data is really darn linear. The formula you seek is +A9*INDEX(LINEST(B2:B8,A2:A8),1)+INDEX(LINEST(B2:B 8,A2:A8),2)

This is Y = X * m + b simple.
__________________
Come on. Let's go space truckin'. Come on!
Reply With Quote
  #5  
Old 02-01-2019, 06:55 PM
Sredni Vashtar's Avatar
Sredni Vashtar Sredni Vashtar is offline
Member
 
Join Date: Mar 2010
Favorite beer: pilseners
Posts: 7,216
Blog Entries: 1
Default

Select both columns, insert a graph/scatter-plot.
Does it look like a line? A curve? A bunch of random dots?
__________________
Sredni Vashtar went forth,
His thoughts were red thoughts and his teeth were white.
His enemies called for peace, but he brought them death.
Sredni Vashtar the Beautiful.

Last edited by Sredni Vashtar; 02-01-2019 at 06:59 PM..
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 07:26 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.22208 seconds with 9 queries