Thread for posting confusing functions

[vividox]

Originally Posted by Gedankenexperiment

Inherited an obscenely large workbook with

That is simply stunning.

[Gedankenexperiment]

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

=SUM(INDEX(ScenarioResultsClm!$C$15:$ZV$1014,$CB22 ,FB$21):INDEX(ScenarioResultsClm!$C$15:$ZV$1014,$C B22,2+FB$21))

and you highlight

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.

[ADoubleDot]

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

[Gedankenexperiment]

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.

[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

[Ozzman]

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.

[limabeanactuary]

GETPIVOTDATA?!?!?!?!

EEEEUUUUURAAAAAAAAAAAAAAAAAAAGH

PEW! PEW!


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

[limabeanactuary]

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

[newts]

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?

[limabeanactuary]

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.