Actuarial Outpost Excel Question
 User Name Remember Me? Password
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

 Enter your email to subscribe to DW Simpson weekly actuarial job updates. li.signup { display: block; text-align: center; text-size: .8; padding: 0px; margin: 8px; float: left; } Entry Level Casualty Health Life Pension All Jobs

 Thread Tools Search this Thread Display Modes
#1
01-30-2019, 05:34 PM
 Cavaliere Member SOA Join Date: May 2016 Posts: 295
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%
#2
01-30-2019, 05:38 PM
 Dr T Non-Fan Member SOA AAA Join Date: Sep 2001 Location: Just outside of Nowhere Posts: 94,315

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
#3
01-30-2019, 05:43 PM
 Childish Gambino Member SOA Join Date: Jul 2014 Posts: 27,111

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."
#4
02-01-2019, 04:40 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,266

Quote:
 Originally Posted by Cavaliere 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!
#5
02-01-2019, 06:55 PM
 Sredni Vashtar Member Join Date: Mar 2010 Favorite beer: pilseners Posts: 7,216 Blog Entries: 1

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..

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 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:23 AM.

 -- Default Style - Fluid Width ---- Default Style - Fixed Width ---- Old Default Style ---- Easy on the eyes ---- Smooth Darkness ---- Chestnut ---- Apple-ish Style ---- If Apples were blue ---- If Apples were green ---- If Apples were purple ---- Halloween 2007 ---- B&W ---- Halloween ---- AO Christmas Theme ---- Turkey Day Theme ---- AO 2007 beta ---- 4th Of July Contact Us - Actuarial Outpost - Archive - Privacy Statement - Top