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

IMMEDIATE NEED - Solvency II Experts - Associate to Partner Level
Browse Solvency II jobs at www.DWSimpson.com/jobs/in/solvency-ii

#211
10-26-2011, 03:05 PM
 campbell Mary Pat Campbell SOA AAA Join Date: Nov 2003 Location: NY Studying for Japanese Favorite beer: Murphy's Irish Stout Posts: 36,189 Blog Entries: 5

Quote:
 Originally Posted by vividox Wow, you've got to be kidding me. Same worksheet: =IF(L3=0,IF(K3=0,0,IF(K3=100,19,IF(K3=300,20,IF(K3 =500,21,IF(K3=800,22,23))))),IF(K3=25,IF(L3=25,1,2 ),IF(K3=50,IF(L3=25,3,IF(L3=50,4,IF(L3=100,5,6))), IF(K3=100,IF(L3=25,7,IF(L3=50,8,IF(L3=100,9,10))), IF(K3=250,IF(L3=25,11,IF(L3=50,12,IF(L3=100,13,IF( L3=250,14,15)))),IF(K3=500,IF(L3=100,16,IF(L3=250, 17,18)),0))))))

Let's see, I think I already submitted the 3rd edition. I'll have to check.
__________________

Now offering online seminars, live seminars, and everything else under the sun for actuarial exams.
#212
10-26-2011, 03:11 PM
 vividox Lead Guitarist Join Date: Dec 2008 Favorite beer: Saison-Brett Posts: 44,235

Quote:
 Originally Posted by campbell Thanks for your contribution! Let's see, I think I already submitted the 3rd edition. I'll have to check.
How many times have I appeared in your literature now?
__________________
This post was crafted using a special blend of herbs and sarcasm.
#213
10-26-2011, 03:16 PM
 campbell Mary Pat Campbell SOA AAA Join Date: Nov 2003 Location: NY Studying for Japanese Favorite beer: Murphy's Irish Stout Posts: 36,189 Blog Entries: 5

Quote:
 Originally Posted by vividox How many times have I appeared in your literature now?
At least twice, I'm thinking.
__________________

Now offering online seminars, live seminars, and everything else under the sun for actuarial exams.
#214
10-26-2011, 03:32 PM
 whisper Member CAS Join Date: Jan 2002 Location: Chicago Favorite beer: Hefewizen Posts: 26,885

Quote:
 Originally Posted by JMO Of course, before writing any nested IF, people should be required to use a structure such as a truth table so that they can analyze and perhaps find a simpler way to express the condition. JMO, you know.
Another important consideration is that if statements can reduce the number of calculations. So, if you have if(Condition, formula 1, formula 2), formula 1 will only trigger if you have the true condition.
#215
10-26-2011, 03:37 PM
 whisper Member CAS Join Date: Jan 2002 Location: Chicago Favorite beer: Hefewizen Posts: 26,885

Quote:
 Originally Posted by limabeanactuary GETPIVOTDATA?!?!?!?!
Is there a more fundamentally wrong idea that MS has created worse than getpivotdata? Especially since MS "helps" you by automatically creating this formula for you?

Never, ever let Excel think for itself. Even when I'm doing a range look-up table, I explicitly create boundaries so I know I'm controlling Excel decision making.
#216
10-26-2011, 03:58 PM
 ADoubleDot Member Join Date: Nov 2007 Location: Slightly Dusty South Studying for the rest of my life Posts: 35,609

I think people crapping on GETPIVOTDATA just don't know how to use it.
__________________
def no_one(the_spanish_inquisition):

**** Juan.
#217
10-26-2011, 04:01 PM
 campbell Mary Pat Campbell SOA AAA Join Date: Nov 2003 Location: NY Studying for Japanese Favorite beer: Murphy's Irish Stout Posts: 36,189 Blog Entries: 5

Please tell me how to use GETPIVOTDATA when you have a pivot table that gets changed.

(If the pivot table never changed, you could always use SUMIFs, etc.)
__________________

Now offering online seminars, live seminars, and everything else under the sun for actuarial exams.
#218
10-26-2011, 04:07 PM
 ADoubleDot Member Join Date: Nov 2007 Location: Slightly Dusty South Studying for the rest of my life Posts: 35,609

Changed like how? Moving stuff around, removing/adding variables. Can you think of a solution where this would be easy to do? I'm familiar enough with the tables and function to do it. But that's not a fair comparison to a lookup table. Where you have to remake the table and the function and both are labor intensive.

Further, I think look up tables contain maintainability issues (copy/paste issues; number of rows changing) and SUMIFs is less clear (unless you are naming your ranges). In the sumifs, i have no idea whats going on without searching though the workbook. In the GPD, I know exactly what's going on, Looking up the dollars for the contract in e1 and the planName in d1.

SUMIFS(\$A\$4:\$A\$8,\$H\$4:\$H\$9,D\$1,\$I\$4:\$I\$9,E\$1)
GETPIVOTDATA("Dollars",\$A\$3,"PlanName",D\$1,"Contra ct",E\$1)
__________________
def no_one(the_spanish_inquisition):

**** Juan.
#219
10-26-2011, 04:14 PM
 whisper Member CAS Join Date: Jan 2002 Location: Chicago Favorite beer: Hefewizen Posts: 26,885

Quote:
 Originally Posted by campbell Please tell me how to use GETPIVOTDATA when you have a pivot table that gets changed.
You'd have to set the Getpivotdata formula elements to ranges that you can adjust as the pivot table adjusts.

It's possible - but the added complexity isn't worth the extra effort.
#220
10-26-2011, 04:21 PM
 vividox Lead Guitarist Join Date: Dec 2008 Favorite beer: Saison-Brett Posts: 44,235

Quote:
 Originally Posted by ADoubleDot Changed like how? Moving stuff around, removing/adding variables. Can you think of a solution where this would be easy to do? I'm familiar enough with the tables and function to do it. But that's not a fair comparison to a lookup table. Where you have to remake the table and the function and both are labor intensive. Further, I think look up tables contain maintainability issues (copy/paste issues; number of rows changing) and SUMIFs is less clear (unless you are naming your ranges). In the sumifs, i have no idea whats going on without searching though the workbook. In the GPD, I know exactly what's going on, Looking up the dollars for the contract in e1 and the planName in d1. SUMIFS(\$A\$4:\$A\$8,\$H\$4:\$H\$9,D\$1,\$I\$4:\$I\$9,E\$1) GETPIVOTDATA("Dollars",\$A\$3,"PlanName",D\$1,"Contra ct",E\$1)
When I use SUMIFs on tables, I just make sure that no other data is in the column and write:

SUMIFS(A:A,H:H,D\$1,I:I,E\$1)

Then you can add as many rows of data as you want and don't have to worry about updating your formulas. Even if you insert columns or copy/paste, the formulas update themselves. The only thing you have to worry about is cut/paste.
__________________
This post was crafted using a special blend of herbs and sarcasm.

 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 02:59 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