Not really a question . . . . . . . . . .

[damian_r]

This is not really a question but I thought I would post it anyway.

What is the best, craziest, longest formula that you have ever come up with and also what is the best spreadsheet you have ever come up with (I guess you can also apply craziest to this one as well).

Give a brief description of what the formula/spreadsheet does with any reply postings.

Damian

 

[clipro]

Interesting "Not really a question..."

In fact one of my best spreadsheets that I ever created was one spreadsheet that is used by one bank. The spreadsheet is infact a plave where they put all credit files in all network of the bank (more than 150 branches and more than few hundred thousand customers).

If that helps you anyway....

 

[MrChris]

=(($W10-((($W10/$X$8)/(((((($W$5/$X$6)-1)*$W$6)+1)*$X$7))*$X$7)*$W$7)*$X$8))*($X$5/$W$5)+((($W10/$X$8)/(((((($W$5/$X$6)-1)*$W$6)+1)*$X$7))*$X$7)*$W$7)*$X$8


but more for the math problem that led me there more than the formula itself.

 

[Smitty]

=IF(ISNA(v(VLOOKUP(RIGHT(OFFSET(INDEX('Page 4'!$B$2:$BA$2,,MATCH($B$1,'Page 4'!$B$2:$BA$2,1)),MATCH($A5,'Page 4'!$A$3:$A$300,0),),4)&" "&RIGHT(OFFSET(INDEX('Page 4'!$B$2:$BA$2,,MATCH($B$1,'Page 4'!$B$2:$BA$2,1)),MATCH($A5,'Page 4'!$A$3:$A$300,0)+1,),4)&" "&RIGHT(OFFSET(INDEX('Page 4'!$B$2:$BA$2,,MATCH($B$1,'Page 4'!$B$2:$BA$2,1)),MATCH($A5,'Page 4'!$A$3:$A$300,0)+2,),4)&" "&RIGHT(OFFSET(INDEX('Page 4'!$B$2:$BA$2,,MATCH($B$1,'Page 4'!$B$2:$BA$2,1)),MATCH($A5,'Page 4'!$A$3:$A$300,0)+3,),4),$AK$4:$AL$18,2,FALSE))),"",v())

It took a while, but it works surprisingly well, especially considering all of the variables it's looking for...Normally I wouldn't do something so heinous, but it addressed the specific need that my boss had.

Smitty

EDIT: It looks at specific premium pages of our publication (Page 4 of 16 in this case), across 52 possible publication dates based upon an input date (the PennySaver is a weekly publication), through a range of 116 individual publication zones, for any number of possible page configurations from 1/4, 1/2 (vertical or horizontal), 3/4 & Full Page and lists them in a cell. I.E. "Tony's Pizza - 1/2V") It gives us an idea of how well a particular market is doing with regards to selling their positions. I'd post an example, but it's confidential as it deals with pre-publication information... :wink:

 

[just_jon]

=IF(AND($N3,G3="C"),IF($N3<>3,INDEX(Miles,MATCH(G$10,MRows,0),MATCH(1,$G3:$K3,0))&" "&INDEX($G$10:$K$10,0,MATCH(1,$G3:$K3,0)),MIN(INDEX(Miles,MATCH(G$10,MRows,0),MATCH(1,$G3:$K3,0)),INDEX(Miles,MATCH(G$10,MRows,0),MATCH(1,$G3:$K3)))&" "&INDEX(MRows,SUMPRODUCT(--(F3:J3=1),--(Sheet2!$B3:$F3=MIN(INDEX(Miles,MATCH(G$10,MRows,0),MATCH(1,$G3:$K3,0)),INDEX(Miles,MATCH(G$10,MRows,0),MATCH(1,$G3:$K3,0)))),--(COLUMN(Sheet2!$B3:$F3)))-COLUMN(Sheet2!$A3),0)),"")

which would have been even longer w/o the defined range names.

And this had a sheet of helper formulas in front! See: http://www.mrexcel.com/board2/viewtopic.php?t=79555&highlight=

 

[Greg Truby]

=IF(OR(D$12="",$A13="o"),"",IF(ISNA(VLOOKUP($B13&","&Trend_OrderType&","&Trend_ProcessStep,Composite1,1,FALSE)),"n/a",IF(ISNA(VLOOKUP($B13,SubTrend_NPCs,1,FALSE)),"No Data",SUMPRODUCT((SubTrend_Months=D$12)*(SubTrend_NPCs=$B13)*SubTrend_Counts*(SubTrend_TAT<=(VALUE(RIGHT(VLOOKUP($B13&","&Trend_OrderType&","&Trend_ProcessStep,TargetsTable,2,FALSE),2)))+IF($C13<>1,0,VLOOKUP($B13&","&Trend_OrderType&","&Trend_ProcessStep,TargetsTable,3,FALSE))))/SUMPRODUCT((SubTrend_Months=D$12)*(SubTrend_NPCs=$B13)*SubTrend_Counts))))

Part of a spreadsheet that tracks our leadtimes to overseas affiliates. The user can pick an order type from a validation list and a process step from another validation list. Change-event handler runs a query to pull part of the data from a large datablock. This formula counts the # of transactions in the query-result data block that match targets for that process step & order type to that customer, which are stored in a lookup table; splitting the results based on month. All of the names are either named ranges, or named formulae.

Smitty -- ¿¿v()?? ¿Qué es eso?

 

[just_jon]

Public Function V(Optional vrnt As Variant) As Variant

' Stephen Dunn
' 2002-09-12

' // The fuction V is used to prevent having to  //
' // calculate the same thing twice, as is:      //
' // =IF(ISERROR(your formula),"",(your formula) //
' // The syntax for use is similar to the above: //
' // =IF(ISNA(V(VLOOKUP(Args))),"",V())          //

Static vrntV As Variant

If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV

End Function

 

[Greg Truby]

And I'm just now seeing this little gem?! This is one that I'll be trying out!!! Thanks, Jon (and Smitty)!

 

[damian_r]

. . . . . . these formulas are enough to make your head spin

 

[Tazguy37]

Public Function V(Optional vrnt As Variant) As Variant

I was wondering what that was, as I read Smitty's post. Neat!