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

Browse New Predictive Modeling Jobs

#151
08-03-2011, 12:41 PM
 vividox Lead Guitarist Join Date: Dec 2008 Favorite beer: Saison-Brett Posts: 44,261

Quote:
Originally Posted by Gedankenexperiment
Inherited an obscenely large workbook with
Quote:
 =IF(F2>0,\$F\$1,IF(G2>0,\$G\$1,IF(H2>0, \$H\$1, IF(I2>0, \$I\$1, IF(J2>0, \$J\$1, IF(K2>0, \$K\$1, IF(L2>0, \$L\$1, IF(M2>0, \$M\$1, IF(N2>0, \$N\$1, IF(O2>0, \$O\$1, IF(P2>0, \$P\$1, IF(Q2>0, \$Q\$1, 0))))))))))))
That is simply stunning.
__________________
This post was crafted using a special blend of herbs and sarcasm.
#152
08-16-2011, 05:00 PM
 Gedankenexperiment Member Join Date: Dec 2010 Posts: 438

So... two very important shortcuts I've stumbled across that my coworkers apparently don't use:

1.) If you highlight part of a function, and press f9, Excel will try to evaluate that part. It will evaluate ranges and arrays too, if they're not super long.

2.) If you highlight part of a function that evaluates to a range, and press f5, Excel will try to take you to that range.

So if you click on a cell with
Quote:
 =SUM(INDEX(ScenarioResultsClm!\$C\$15:\$ZV\$1014,\$CB22 ,FB\$21):INDEX(ScenarioResultsClm!\$C\$15:\$ZV\$1014,\$C B22,2+FB\$21))
and you highlight
Quote:
 INDEX(ScenarioResultsClm!\$C\$15:\$ZV\$1014,\$CB22,FB\$2 1):INDEX(ScenarioResultsClm!\$C\$15:\$ZV\$1014,\$CB22,2 +FB\$21)
and press f5, Excel will automatically evaluate the whole thing and take you to the range that is finally being indexed.
Which in this case happens to just be 3 cells, ScenarioResultsClm!ID15:IF15.

Last edited by Gedankenexperiment; 08-16-2011 at 05:08 PM..
#153
08-16-2011, 05:26 PM
 ADoubleDot Member Join Date: Nov 2007 Location: Slightly Dusty South Studying for the rest of my life Posts: 35,623

Quote:
 Originally Posted by vividox That is simply stunning.
=INDEX(F2:Q2,1,MATCH(">0",\$F\$1:\$Q\$1,1))

maybe, i didn't test it
__________________
def no_one(the_spanish_inquisition):

**** Juan.
#154
08-16-2011, 10:43 PM
 Gedankenexperiment Member Join Date: Dec 2010 Posts: 438

Quote:
 Originally Posted by ADoubleDot =INDEX(F2:Q2,1,MATCH(">0",\$F\$1:\$Q\$1,1)) maybe, i didn't test it
The real killer was that F1:Q1={1,2,3,4,5...}

Though to be fair there was something a little tricky about it, the numbers weren't ordered or something.
#155
08-19-2011, 04:37 PM
 ADoubleDot Member Join Date: Nov 2007 Location: Slightly Dusty South Studying for the rest of my life Posts: 35,623

This is what i did today:

=IFERROR(IF(\$F\$3="(All)",GETPIVOTDATA("myPaid",Seg mentPivot!\$A\$5,"agg_seg_cd",\$C6,"Quarters",RIGHT(F \$5)*1,"Years",LEFT(F\$5,4)*1),GETPIVOTDATA("myPaid" ,SegmentPivot!\$A\$5,"agg_seg_cd",\$C6,"Quarters",RIG HT(F\$5)*1,"Years",LEFT(F\$5,4)*1,"Program",\$F\$3)),0 )-IFERROR(IF(\$F\$3="(All)",GETPIVOTDATA("RPTG_NET_PAI D_AMT",SegmentPivot!\$A\$24,"agg_seg_cd",\$C6,"Quarte rs",RIGHT(F\$5)*1,"Years",LEFT(F\$5,4)*1),GETPIVOTDA TA("RPTG_NET_PAID_AMT",SegmentPivot!\$A\$24,"agg_seg _cd",\$C6,"Quarters",RIGHT(F\$5)*1,"Years",LEFT(F\$5, 4)*1,"case_program",\$F\$3)),0)+IFERROR(IF(\$F\$3="(Al l)",GETPIVOTDATA("calc_paid_save",SegmentPivot!\$A\$ 40,"agg_seg_cd",\$C6,"Quarters",RIGHT(F\$5)*1,"Years ",LEFT(F\$5,4)*1),GETPIVOTDATA("calc_paid_save",Seg mentPivot!\$A\$40,"agg_seg_cd",\$C6,"Quarters",RIGHT( F\$5)*1,"Years",LEFT(F\$5,4)*1,"Program",\$F\$3)),0)+I FERROR(INDEX(FacilityPivot!\$D\$32:\$O\$46,MATCH(\$F\$3& \$C6,FacilityPivot!\$A\$32:\$A\$46,0),MATCH(F\$5,Facilit yPivot!\$D\$19:\$O\$19,0)),0)

i was in a hurry and it just had to work
__________________
def no_one(the_spanish_inquisition):

**** Juan.
#156
08-19-2011, 06:15 PM
 Ozzman Member SOA Join Date: Apr 2011 Studying for P College: Xavier Favorite beer: Free Posts: 312

Quote:
 Originally Posted by ADoubleDot This is what i did today: =IFERROR(IF(\$F\$3="(All)",GETPIVOTDATA("myPaid",Seg mentPivot!\$A\$5,"agg_seg_cd",\$C6,"Quarters",RIGHT(F \$5)*1,"Years",LEFT(F\$5,4)*1),GETPIVOTDATA("myPaid" ,SegmentPivot!\$A\$5,"agg_seg_cd",\$C6,"Quarters",RIG HT(F\$5)*1,"Years",LEFT(F\$5,4)*1,"Program",\$F\$3)),0 )-IFERROR(IF(\$F\$3="(All)",GETPIVOTDATA("RPTG_NET_PAI D_AMT",SegmentPivot!\$A\$24,"agg_seg_cd",\$C6,"Quarte rs",RIGHT(F\$5)*1,"Years",LEFT(F\$5,4)*1),GETPIVOTDA TA("RPTG_NET_PAID_AMT",SegmentPivot!\$A\$24,"agg_seg _cd",\$C6,"Quarters",RIGHT(F\$5)*1,"Years",LEFT(F\$5, 4)*1,"case_program",\$F\$3)),0)+IFERROR(IF(\$F\$3="(Al l)",GETPIVOTDATA("calc_paid_save",SegmentPivot!\$A\$ 40,"agg_seg_cd",\$C6,"Quarters",RIGHT(F\$5)*1,"Years ",LEFT(F\$5,4)*1),GETPIVOTDATA("calc_paid_save",Seg mentPivot!\$A\$40,"agg_seg_cd",\$C6,"Quarters",RIGHT( F\$5)*1,"Years",LEFT(F\$5,4)*1,"Program",\$F\$3)),0)+I FERROR(INDEX(FacilityPivot!\$D\$32:\$O\$46,MATCH(\$F\$3& \$C6,FacilityPivot!\$A\$32:\$A\$46,0),MATCH(F\$5,Facilit yPivot!\$D\$19:\$O\$19,0)),0) i was in a hurry and it just had to work
This wins.
#157
08-20-2011, 07:43 AM
 limabeanactuary Mary Pat Campbell Join Date: Jan 2010 Studying for Anglo-Saxon Favorite beer: Bass Ale Posts: 14,060

GETPIVOTDATA?!?!?!?!

EEEEUUUUURAAAAAAAAAAAAAAAAAAAGH

PEW! PEW!

You had better believe that one is showing up in a future CompAct.
__________________

Now offering online seminars, live seminars, and everything else under the sun and over the moon for actuarial exams.
#158
08-20-2011, 07:45 AM
 limabeanactuary Mary Pat Campbell Join Date: Jan 2010 Studying for Anglo-Saxon Favorite beer: Bass Ale Posts: 14,060

Oh, and thanks for all these examples, guys.

The one coming out in October (at the annual meeting) doesn't have any AO content, I think - I'm featuring stuff from spreadsheet-related groups I'm a part of (after I showed them what y'all did, and asked for more horrid formulas...they delivered.)
__________________

Now offering online seminars, live seminars, and everything else under the sun and over the moon for actuarial exams.
#159
08-20-2011, 02:01 PM
 newts Member Join Date: Jul 2007 Posts: 229

Quote:
 Originally Posted by limabeanactuary GETPIVOTDATA?!?!?!?! EEEEUUUUURAAAAAAAAAAAAAAAAAAAGH PEW! PEW! You had better believe that one is showing up in a future CompAct.
What's wrong with getpivotdata?

As long as you aren't changing the data, row, and column information, why isn't it useful?
#160
08-20-2011, 02:10 PM
 limabeanactuary Mary Pat Campbell Join Date: Jan 2010 Studying for Anglo-Saxon Favorite beer: Bass Ale Posts: 14,060

Quote:
 Originally Posted by newts What's wrong with getpivotdata? As long as you aren't changing the data, row, and column information, why isn't it useful?

The whole =point= of pivot tables is easy-to-change slice-and-dice of data. Their inherent nature involves change.

Now, it's possible that you've created groupings or calculated fields in the pivot table that it's not easy to do sumif/countif on the underlying data, but in general it's better to do that sumif/countif.
__________________

Now offering online seminars, live seminars, and everything else under the sun and over the moon for actuarial exams.

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