Actuarial Outpost Thread for posting confusing functions
 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

 DW Simpson & CoWorldwide ActuarialRecruitment Entry Level JobsCasualty, Health, Life, Pension,Investment --Insurance / Consulting Asian JobsHong Kong, China, India, Japan, Korea, Indonesia, Singapore,Malaysia, and more Registration FormBe Notified of New Actuarial Jobs

 Thread Tools Display Modes
#71
03-31-2011, 02:26 PM
 vividox Lead Guitarist Join Date: Dec 2008 Favorite beer: Saison-Brett Posts: 44,235

I've never had a problem with debugging. Run Evaluate Formula once to see where everything is coming from and you pretty much know all there is to know about the formula. Then you change it from there.
__________________
This post was crafted using a special blend of herbs and sarcasm.
#72
03-31-2011, 04:24 PM
 Gedankenexperiment Member Join Date: Dec 2010 Posts: 438

Quote:
 Originally Posted by vividox =IF(T3="TTT",VLOOKUP(U3,Rates!\$A\$153:\$G\$289,IF(C3= 24,2,IF(C3=3,4,6)),FALSE)+IF(U3>"670",0,VLOOKUP(V3 ,Rates!\$A\$153:\$G\$289,IF(C3=24,2,IF(C3=3,4,6)),FALS E)),0)+IF(T3="PPT",VLOOKUP(U3,Rates!\$I\$153:\$O\$164, IF(C3=24,2,IF(C3=3,4,6)),FALSE),0)+IF(T3="OTHER",V LOOKUP(U3,Rates!\$I\$168:\$O\$176,IF(C3=24,2,IF(C3=3,4 ,6)),FALSE)+VLOOKUP(V3,Rates!\$I\$168:\$O\$176,IF(C3=2 4,2,IF(C3=3,4,6)),FALSE)) and while lengthy, it's really just a glorified VLOOKUP. Would you consider this a megaformula or are you thinking more along the lines of a lot of logic and combination of formulas?
Yeah, mess. Not that confusing, but also why use excel in the first place if you're not gonna show any work? You need someone to slap your hand with a ruler.

Also I find numbers written and compared as strings to be disturbing. It bothers me when "4000"<"670".

Last edited by Gedankenexperiment; 03-31-2011 at 04:32 PM..
#73
03-31-2011, 04:33 PM
 ADoubleDot Member Join Date: Nov 2007 Location: Slightly Dusty South Studying for the rest of my life Posts: 35,609

Quote:
 Originally Posted by vividox I guess there is a great deal of ambiguity when saying "megaformula" as well. What do you consider a megaformula? This is in one of my first worksheets I made: =IF(T3="TTT",VLOOKUP(U3,Rates!\$A\$153:\$G\$289,IF(C3= 24,2,IF(C3=3,4,6)),FALSE)+IF(U3>"670",0,VLOOKUP(V3 ,Rates!\$A\$153:\$G\$289,IF(C3=24,2,IF(C3=3,4,6)),FALS E)),0)+IF(T3="PPT",VLOOKUP(U3,Rates!\$I\$153:\$O\$164, IF(C3=24,2,IF(C3=3,4,6)),FALSE),0)+IF(T3="OTHER",V LOOKUP(U3,Rates!\$I\$168:\$O\$176,IF(C3=24,2,IF(C3=3,4 ,6)),FALSE)+VLOOKUP(V3,Rates!\$I\$168:\$O\$176,IF(C3=2 4,2,IF(C3=3,4,6)),FALSE)) and while lengthy, it's really just a glorified VLOOKUP. Would you consider this a megaformula or are you thinking more along the lines of a lot of logic and combination of formulas?
That's a mess. I wouldn't hardcode things like "TTT" in case that flag ever needed to change. Or if you wanted to apply the same calculation to a different whatever-the-****-that-is.

Most evident though, is that the whole thing could be replaced by index(match,match) which would be less retarded than what you are doing.
__________________
def no_one(the_spanish_inquisition):

**** Juan.
#74
03-31-2011, 04:39 PM
 vividox Lead Guitarist Join Date: Dec 2008 Favorite beer: Saison-Brett Posts: 44,235

Quote:
 Originally Posted by Gedankenexperiment Yeah, mess. Not that confusing, but also why use excel in the first place if you're not gonna show any your work? Also I find numbers written and matched as strings to be disturbing. It bothers me when "4000"<"670".
Not sure if I really understand your second sentence. In this particular case, I'm looking up factors for a commercial auto book of business. All this formula is doing is looking up the physical damage factor (which is, of course, dependent of the type of vehicle we are talking about, hence the funky logic). There are many, many, many other factors that go into rating, so drawing out the retrieval of a single factor seems like a waste of space to me.

This is showing my work, in this instance, since I'm showing all the individual factors for given policy types/limits, then using them to calculate the final premium.
__________________
This post was crafted using a special blend of herbs and sarcasm.
#75
03-31-2011, 04:44 PM
 vividox Lead Guitarist Join Date: Dec 2008 Favorite beer: Saison-Brett Posts: 44,235

Quote:
 Originally Posted by ADoubleDot That's a mess. I wouldn't hardcode things like "TTT" in case that flag ever needed to change. Or if you wanted to apply the same calculation to a different whatever-the-****-that-is. Most evident though, is that the whole thing could be replaced by index(match,match) which would be less retarded than what you are doing.
Yeah, same thing with the overabundance of IF(C3=3,4,6) and such. If my columns ever change, that's a lot of formula editing. Like I said, this was one of my first projects and I did a lot of stupid things on this worksheet. It still manages to get the job done.

I'm not sure how it could be replaced by INDEX(MATCH,MATCH) though, each one of the VLOOKUPs is going to a different table.
__________________
This post was crafted using a special blend of herbs and sarcasm.
#76
03-31-2011, 04:47 PM
 epeddy1 Member Join Date: Jan 2010 Location: Midwest Posts: 1,887

Quote:
 Originally Posted by vividox I guess there is a great deal of ambiguity when saying "megaformula" as well. What do you consider a megaformula? This is in one of my first worksheets I made: =IF(T3="TTT",VLOOKUP(U3,Rates!\$A\$153:\$G\$289,IF(C3= 24,2,IF(C3=3,4,6)),FALSE)+IF(U3>"670",0,VLOOKUP(V3 ,Rates!\$A\$153:\$G\$289,IF(C3=24,2,IF(C3=3,4,6)),FALS E)),0)+IF(T3="PPT",VLOOKUP(U3,Rates!\$I\$153:\$O\$164, IF(C3=24,2,IF(C3=3,4,6)),FALSE),0)+IF(T3="OTHER",V LOOKUP(U3,Rates!\$I\$168:\$O\$176,IF(C3=24,2,IF(C3=3,4 ,6)),FALSE)+VLOOKUP(V3,Rates!\$I\$168:\$O\$176,IF(C3=2 4,2,IF(C3=3,4,6)),FALSE)) and while lengthy, it's really just a glorified VLOOKUP. Would you consider this a megaformula or are you thinking more along the lines of a lot of logic and combination of formulas?
I'll give a simple example. Lets say that you're calculating the PV of a stream of cash flows. You need the following functions:
• Your "n" requires a few functions to correspond to the current date, convert years into quarters, and whatever else. Lets just say there are few different "if" statements and "vlookups" to determine the effective "current" date.
• Your interest rate isn't constant over time, and requires some vlookups to pull rates from another tab that contains the curve and convert them to "convertible quarterly" or whatever.
• Your cash flows have an if statement and a vlookup, and maybe some function to manipulate the amounts some way.
• Combine all previous with your favorite PV formula.

Again, this is a simplified example. I personally like to perform different functions in different columns so I can make sure the results are what I want. If all the formulas are imbedded into one cell, there's really no way to make sure the "n", rates, and cash flows are reasonable before it spits out the PV. If you ever have to go back to the spreadsheet, and say this time around you don't want "convertible quarterly" interest rates and want "convertible semi", it's easier to find which column you don't need anymore and fix it that way, rather than digging through a megaformula and making sure all the logic is still kosher. If it looks sloppy with all the columns, I can make it pretty by having a "summary" tab that summarizes the interesting columns.

Just my humble opinion.
__________________
P FM MFE MLC C VEE Econ VEE Fin VEE Stat
FAP
Quote:
 Originally Posted by 3rookie I agree with epeddy's comments.
Quote:
 Originally Posted by GeneralTso oh wow. epeddy's advice is incredible.
The story of my life: It's not that I'm in denial of being a nerd. It's that everyone else is in denial of how incredibly cool I am.

Last edited by epeddy1; 03-31-2011 at 04:51 PM..
#77
03-31-2011, 04:51 PM
 vividox Lead Guitarist Join Date: Dec 2008 Favorite beer: Saison-Brett Posts: 44,235

Quote:
 Originally Posted by epeddy1 If all the formulas are imbedded into one cell, there's really no way to make sure the "n", rates, and cash flows are reasonable before it spits out the PV.
Sure there is. "Evaluate Formula" on the Formulas ribbon does exactly that.
__________________
This post was crafted using a special blend of herbs and sarcasm.
#78
03-31-2011, 05:15 PM
 Gedankenexperiment Member Join Date: Dec 2010 Posts: 438

Quote:
 Originally Posted by vividox Not sure if I really understand your second sentence. In this particular case, I'm looking up factors for a commercial auto book of business. All this formula is doing is looking up the physical damage factor (which is, of course, dependent of the type of vehicle we are talking about, hence the funky logic). There are many, many, many other factors that go into rating, so drawing out the retrieval of a single factor seems like a waste of space to me. This is showing my work, in this instance, since I'm showing all the individual factors for given policy types/limits, then using them to calculate the final premium.
IF(U3>670,1,0) is very different than IF(U3>"670",1,0).

Quote:
 This is showing my work, in this instance, since I'm showing all the individual factors for given policy types/limits, then using them to calculate the final premium.
Showing your work would be like.
(Factors)
PPT 0
TTT 30
Other 50
----
Prem 80

It's not necessary, but the whole beauty of spreadsheets is that you get to see every step. It's easier to spot errors (because you might not notice Prem is off) and easier to read (because you don't have to evaluate the formula or even know what "VLOOKUP" means to see what's happening). Plus, you might want to do something else with those factors.

Last edited by Gedankenexperiment; 03-31-2011 at 05:28 PM..
#79
03-31-2011, 05:28 PM
 vividox Lead Guitarist Join Date: Dec 2008 Favorite beer: Saison-Brett Posts: 44,235

Quote:
 Originally Posted by Gedankenexperiment IF(U3>670,1,0) is very different than IF(U3>"670",1,0).
Right. The inputs are cropped numbers, so they are text strings, not numbers. They are also all 3 digit text strings, conveniently enough.

Quote:
 Originally Posted by Gedankenexperiment Showing your work would be like. (Factors) PPT 0 TTT 30 Other 50 ---- Prem 80 It's not necessary, but the whole beauty of spreadsheets is that you get to see every step. It's easier to spot errors (because you might not notice Prem is off) and easier to read (because you don't have to evaluate the formula or even know what "VLOOKUP" means to see what's happening).
I get what you are saying in general, and in general I would agree with you. However, in this case, I'm calculating a different policy on every row, and that policy is either a TTT, PPT, or Other, exclusive. So the formula may reduce to =0+0+80 or =0+80+0 but it's impossible for it to be anything like =0+30+50. Using a plus here was simply easier than using a super long conditional IF.
__________________
This post was crafted using a special blend of herbs and sarcasm.
#80
03-31-2011, 05:46 PM
 Gedankenexperiment Member Join Date: Dec 2010 Posts: 438

Quote:
 Originally Posted by Gedankenexperiment IF(U3>670,1,0) is very different than IF(U3>"670",1,0). Right. The inputs are cropped numbers, so they are text strings, not numbers. They are also all 3 digit text strings, conveniently enough.
Ohyeah, car insurance has all those little number chunks. My policy is rated 02R284AZ93OMFGBBQ2!

Quote:
 Originally Posted by vividox I get what you are saying in general, and in general I would agree with you. However, in this case, I'm calculating a different policy on every row, and that policy is either a TTT, PPT, or Other, exclusive. So the formula may reduce to =0+0+80 or =0+80+0 but it's impossible for it to be anything like =0+30+50. Using a plus here was simply easier than using a super long conditional IF.
I see, I would have probably moved the tables to align horizontally or vertically, so they could be in a single lookup. But maybe you couldn't move them. Not a huge deal either way.

 Thread Tools 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 05:52 PM.

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

Powered by vBulletin®
Copyright ©2000 - 2013, 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.25905 seconds with 8 queries