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



## damian_r (Jun 24, 2005)

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 (Jun 24, 2005)

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 (Jun 24, 2005)

=(($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 (Jun 26, 2005)

=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 (Jun 26, 2005)

=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 (Jun 27, 2005)

=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 (Jun 27, 2005)

```
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 (Jun 27, 2005)

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


----------



## damian_r (Jun 27, 2005)

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


----------



## Tazguy37 (Jun 27, 2005)

just_jon said:
			
		

> ```
> Public Function V(Optional vrnt As Variant) As Variant
> ```



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


----------



## just_jon (Jun 28, 2005)

I found out about V here.


----------



## Felix Atagong (Jun 28, 2005)

just_jon said:
			
		

> ```
> Public Function V(Optional vrnt As Variant) As Variant
> ```



That V function really gave me an Excelgasm    - wow - gonna use it often if my wife permits...


----------



## Fergus (Jul 7, 2005)

I used this formula to convert numbers to words by referring to "number texts" in a lookup table. That was before I discovered vba versions. A tender we submitted required the rates for all items to be written in words and there were over 2000 items! 
	
	
	
	
	
	



```
=IF(G15=0,"Nil",IF(LEN(G15)>6,VLOOKUP(VALUE(LEFT(G15,(LEN(G15)-6))),Numbers!$A$1:$J$999,10,FALSE)&IF(VALUE(MID(G15,LEN(G15)-5,3))>0,VLOOKUP(VALUE(MID(G15,LEN(G15)-5,3)),Numbers!$A$1:$H$999,8,FALSE),)&IF(AND(VALUE(RIGHT(G15,3))>0,VALUE(RIGHT(G15,3))<100),"and "&VLOOKUP(VALUE(RIGHT(G15,3)),Numbers!$A$1:$F$999,6,FALSE),IF(VALUE(RIGHT(G15,3))=0,,VLOOKUP(VALUE(RIGHT(G15,3)),Numbers!$A$1:$F$999,6,FALSE))),IF(LEN(G15)>3,VLOOKUP(VALUE(LEFT(G15,(LEN(G15)-3))),Numbers!$A$1:$H$999,8,FALSE)&IF(VALUE(RIGHT(G15,3))>0,IF(VALUE(RIGHT(G15,3))<100,"and "&VLOOKUP(VALUE(RIGHT(G15,3)),Numbers!$A$1:$F$999,6,FALSE),VLOOKUP(VALUE(RIGHT(G15,3)),Numbers!$A$1:$F$999,6,FALSE)),""),IF(LEN(G15)<4,IF(VALUE(G15)>0,IF(VALUE(G15)<100,VLOOKUP(VALUE(G15),Numbers!$A$1:$F$999,6,FALSE),VLOOKUP(VALUE(G15),Numbers!$A$1:$F$999,6,FALSE)),""))))&"Yen")
```


----------



## Smitty (Jul 7, 2005)

> A tender we submitted required the rates for all items to be written in words and there were over 2000 items!


Reminds me of the Guiness commercial I saw recently: "Brilliant, just brilliant!"   

Smitty


----------



## colcol (Jul 31, 2005)

Greetings,

A number of years ago a former boss called me to prepare a spreadsheet that he needed to analyze certain aspects of a large project he was involved with.  I was fairly new to Excel at the time and pulled my hair out trying to figure out how to get the workbook to do what they needed.  To make a long story short, he called me several weeks later to inform me that they saved over 1.2 million dollars with file.  Needless to say that sparked more interest for me into the wild and wonderful world of Excel.

Alan


----------



## Greg Truby (Oct 19, 2005)

Just in case anyone needs to know -- the V() function that Smitty & Jon referred to can be nested and still work properly, i.e. you can write 

=IF(ISNA(v(VLOOKUP(Args1))),IF(ISNA(v(VLOOKUP(Args2))),IF(ISNA(v(VLOOKUP(Args3))),"Not Found",v()),v()),v()) 

and it will return the proper values.  What I found interesting was that if I set a breakpoint in there and step through the recursions the call stack does not augment, each nested step runs as an independent call to the function.  Makes sense I suppose since nested built-in's get treated independently.  Learned more from that simple little function than looking at the humdingers we all posted. A real gem boys, thanks again for mentioning it Smitty and for posting the code, Jon.


----------



## tactps (Oct 19, 2005)

I'm shocked and stunned at you all - I thought the idea was to shorten formulas (lol).

Especially you Smitty - with an "OFFSET" - I'm truly disillusioned.

I think I might crawl into a ball until nightfall.


----------



## Smitty (Oct 19, 2005)

> I'm shocked and stunned at you all - I thought the idea was to shorten formulas (lol).
> 
> Especially you Smitty - with an "OFFSET" - I'm truly disillusioned.


Ahhhh, git stuffed!   Wasn't this a post about the most outrageous one?

A year later, I "could" probably figure out a way to make that monster work without making the WB any bigger, but why?  It works like champ, and I'm happy to have my hands out of that **** thing anyway!   Freaking needy sales people!  (And of course their one person for this office who has access to the wb is in Spain unexpectedly, so guess who else has access to read/write?  So I'm in it again!)   AAAAAGH!  The sad thing is after I built that one summary sheet (note I only posted 1 formula!), I was pretty much the only one to EVER look at the results!  So I proved, once again that, just because you can prove it's possible when "they" (the ubiquitious "they") ask if it is, the same "they" don't use it, but the good thing is that the same "they" signed my paycheck this week. 

Although, I think we should be nice and be happy that Aladin hasn't reduced all of those to LEN<100 (just for fun of course). 

See ya,

Smitty

How do you "unexpectedly" find yourself in Spain?  I mean Tijuana from San Diego I can understand; hell, I've woken up on the wrong side of the border when I lived in Texas, but Spain?


----------



## tactps (Oct 19, 2005)

Your feathers are too easy to ruffle, Smitty!    

Keep well, buddy! :wink:


----------



## Smitty (Oct 20, 2005)

> Your feathers are too easy to ruffle


Fair Dinkum, you know me better than that, Mate! 

Smitty


----------



## adulted (Jun 25, 2006)

*Current Calendar*

Can't remember where I got this one from:

B1 =now()  formattted as Month 2006 (mmmm yyyy)
B2:H2  Sun, Mon, Tue, Wed, Thu, Fri, Sat
Select range B3:H7 enter the following:

=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))<>MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-
(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+
{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(NOW()),
MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),
MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)

CSE
formatted as custom d

PaulExcel Formulas.xlsABCDEFGHI1June 20062SunMonTueWedThuFriSat3    1234456789105111213141516176181920212223247252627282930 Month Calendar


----------



## Greg Truby (Jun 26, 2006)

Well, while it certainly resembles the skill-level of a few board members I could name, I personally hadn't seen it before.  Nifty handiwork by whoever did indeed stitch it together.


----------



## njimack (Jun 28, 2006)

See comment 6 on http://www.dicks-blog.com/archives/2004/06/23/ugly-formulas/


----------



## SydneyGeek (Feb 27, 2008)

Greg Truby said:


> Well, while it certainly resembles the skill-level of a few board members I could name, I personally hadn't seen it before. Nifty handiwork by whoever did indeed stitch it together.


 
Not entirely sure, but John Walkenbach put together a calendar formula in one of his books. It may be the same one...

Denis


----------



## Richard Schollar (Feb 27, 2008)

Denis, nobody could ever accuse you of being too hasty when it comes to formulating a reply...


----------



## SydneyGeek (Feb 27, 2008)

RichardSchollar said:


> Denis, nobody could ever accuse you of being too hasty when it comes to formulating a reply...


 
Yep, my middle name's Quick-draw! 

Actually, I kinda got directed to that post tonight and thought I'd throw in my 2 cents...

Denis


----------



## MarkAndrews (Feb 28, 2008)

A few from me

=IF(ISNUMBER(D5),IF(N(E5),E5,IF(SUMIF(E5,"*(*",D5),LEFT(E5,SEARCH("(",E5&"(")-1),LEFT(E5,SUMPRODUCT(LEN(E5)-LEN(SUBSTITUTE(E5,{0,1,2,3,4,5,6,7,8,9},""))))))/D5,"")

=TRIM(TEXT(EOMONTH($D$3,MID($C4,SEARCH("-",$C4,1),4)),"DD MMM YYY"))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o> 
<o>=IF(YEAR(G$14)=2007,NETWORKDAYS(EOMONTH(G14,-1)+1,EOMONTH(G14,0),Hol07),IF(YEAR(G$14)=2008,NETWORKDAYS(EOMONTH(G14,-1)+1,EOMONTH(G14,0),Hol08),IF(YEAR(G$14)=2009,NETWORKDAYS(EOMONTH(G14,-1)+1,EOMONTH(G14,0),Hol09),IF(YEAR(G$14)=2010,NETWORKDAYS(EOMONTH(G14,-1)+1,EOMONTH(G14,0),Hol10),IF(YEAR(G$14)=2011,NETWORKDAYS(EOMONTH(G14,-1)+1,EOMONTH(G14,0),Hol11),IF(YEAR(G$14)=2012,NETWORKDAYS(EOMONTH(G14,-1)+1,EOMONTH(G14,0),Hol12)))))))

=IF(ISNA(HLOOKUP(MID($A6,6,2),'[Brand_rebates Retail.xls]Report Feed'!$C$5:$P$30,MATCH("Grand Total",'[Brand_rebates Retail.xls]Report Feed'!$A$5:$A$30,0),0)),"0",HLOOKUP(MID($A6,6,2),'[Brand_rebates Retail.xls]Report Feed'!$C$5:$P$30,MATCH("Grand Total",'[Brand_rebates Retail.xls]Report Feed'!$A$5:$A$30,0),0))<o></o>
<o></o> 
<o>=IF(ISNA(VLOOKUP(LEFT(G3,SEARCH(" ",G3)-1),'[Copy of Supplier Payment Terms.xls]Live'!$A:$H,3,0)),"",IF(ISERROR(VLOOKUP(LEFT(G3,SEARCH(" ",G3)-1),'[Copy of Supplier Payment Terms.xls]Live'!$A:$H,3,0)),"",VLOOKUP(LEFT(G3,SEARCH(" ",G3)-1),'[Copy of Supplier Payment Terms.xls]Live'!$A:$H,3,0)))<o></o>
</o></o>


----------



## Lewiy (Feb 28, 2008)

One I’ve just had to construct for verifying that data has been correctly input into a worksheet:

=IF(A3="","",IF(OR(LEN(C3)<>6,AND(LEFT(C3,1)<>"E",LEFT(C3,1)<>"C")),"A/C#",IF(AND(LEFT(C3)="E",OR(G3="",F3<>"")),"Euroclear",IF(AND(LEFT(C3)="C",OR(G3<>"",F3="")),"Clearstream",IF(OR(AND(LEFT(A3)="1",OR(K3<>"X",J3<>"")),AND(LEFT(A3)="2",OR(K3<>"",J3<>"X"))),"MTN/ECP",IF(SUM(F3:G3)<>P3,"Value",""))))))&" - "&IF(A3="","",IF(OR(AND(D3<>H3,ISERROR(FIND("S",O3))),AND(NOT(ISERROR(FIND("S",O3))),D3=H3)),"Swap?",IF(OR(AND(B3=E3,ISERROR(FIND("V",O3))),AND(NOT(ISERROR(FIND("V",O3))),B3<>E3)),"Check Issuer",IF(N3<>VALUE(RIGHT(CELL("FILENAME",T3),LEN(CELL("FILENAME",T3))-FIND("]",CELL("FILENAME",T3)))),"Date",IF(COUNTIF(A:A,A3)>1,"Duplicate","")))))&" - "&IF(OR(AND(LEFT(E3,3)="RBS",LEN(E3)<>8),AND(E3="ULSTER",LEN(E3)<>11)),"PROGRAMME NUMBER",IF(OR(E3="BANK SCOTLAND",E3="WESTPAC",E3="NORD DLB"),"ISSUER ENTITY",""))


----------



## MarkAndrews (Feb 28, 2008)

Lewiy said:


> One I’ve just had to construct for verifying that data has been correctly input into a worksheet:
> 
> =IF(A3="","",IF(OR(LEN(C3)<>6,AND(LEFT(C3,1)<>"E",LEFT(C3,1)<>"C")),"A/C#",IF(AND(LEFT(C3)="E",OR(G3="",F3<>"")),"Euroclear",IF(AND(LEFT(C3)="C",OR(G3<>"",F3="")),"Clearstream",IF(OR(AND(LEFT(A3)="1",OR(K3<>"X",J3<>"")),AND(LEFT(A3)="2",OR(K3<>"",J3<>"X"))),"MTN/ECP",IF(SUM(F3:G3)<>P3,"Value",""))))))&" - "&IF(A3="","",IF(OR(AND(D3<>H3,ISERROR(FIND("S",O3))),AND(NOT(ISERROR(FIND("S",O3))),D3=H3)),"Swap?",IF(OR(AND(B3=E3,ISERROR(FIND("V",O3))),AND(NOT(ISERROR(FIND("V",O3))),B3<>E3)),"Check Issuer",IF(N3<>VALUE(RIGHT(CELL("FILENAME",T3),LEN(CELL("FILENAME",T3))-FIND("]",CELL("FILENAME",T3)))),"Date",IF(COUNTIF(A:A,A3)>1,"Duplicate","")))))&" - "&IF(OR(AND(LEFT(E3,3)="RBS",LEN(E3)<>8),AND(E3="ULSTER",LEN(E3)<>11)),"PROGRAMME NUMBER",IF(OR(E3="BANK SCOTLAND",E3="WESTPAC",E3="NORD DLB"),"ISSUER ENTITY",""))


 
Beats my efforts!


----------



## Lewiy (Feb 29, 2008)

MarkAndrews said:


> Beats my efforts!


 
There’s probably a more efficient way of doing what it does but it has just grown gradually over the last week or so as I kept adding bits to verify different stuff!!!


----------



## MarkAndrews (Feb 29, 2008)

Lewiy said:


> There’s probably a more efficient way of doing what it does but it has just grown gradually over the last week or so as I kept adding bits to verify different stuff!!!


 
As daft as this is going to sound, I like nothing more than looking at other peoples formula's, breaking them down etc - I find it increases my knowledge sometimes and opens up other avenues to explore with formulae


----------



## Lewiy (Feb 29, 2008)

MarkAndrews said:


> As daft as this is going to sound, I like nothing more than looking at other peoples formula's, breaking them down etc - I find it increases my knowledge sometimes and opens up other avenues to explore with formulae


 
I completely agree…although sometimes it helps to know what the source data looks like…otherwise it can leave you more baffled and confused than when you started!!!


----------



## Harley78 (Feb 29, 2008)

My goodness,,, how does one figure these out dangggggggggg


----------



## MarkAndrews (Feb 29, 2008)

Lewiy said:


> I completely agree…although sometimes it helps to know what the source data looks like…otherwise it can leave you more baffled and confused than when you started!!!


 
Couldn't agree more Lewiy, although sometimes you can guess what some of it looks like from the formula (Time, Eomonth etc), or the ranges used


----------



## Domski (Feb 29, 2008)

MarkAndrews said:


> As daft as this is going to sound, I like nothing more than looking at other peoples formula's, breaking them down etc - I find it increases my knowledge sometimes and opens up other avenues to explore with formulae


 
It takes me long enough to figure out some of my own formula, especially after a particularly heavy weekender


----------



## gingerafro (Feb 29, 2008)

Greg Truby said:


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



And I'm only just seeing it now....!! That'll save so much space with my extensive overuse of isna and getpivotdata.  Wow.


----------



## MarkAndrews (Feb 29, 2008)

Domski said:


> It takes me long enough to figure out some of my own formula, especially after a particularly heavy weekender


 
You need to book some more AA meetings mate.....

**me too **


----------



## Domski (Feb 29, 2008)

I'm giving up the cigs next week. One step at a time


----------



## MarkAndrews (Feb 29, 2008)

Domski said:


> I'm giving up the cigs next week. One step at a time


 
I was in <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">Leeds</st1lace> the other week mate, Revolution (Top), Courtyard, Sports Bar, Squares, Yates (Bottom) & Flares!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Had a brilliant night, last train home mind, back to ‘wonderful’ <st1lace w:st="on">North Yorkshire</st1lace><o></o>
<o> </o>
5pm – 5am drinking & No Hangover!! RESULT!<o></o>


----------



## schielrn (Feb 29, 2008)

=IF(ISBLANK(C2),ROUND(INDEX(Reserve!A:A,MATCH(A2,Reserve!W:W,0)+1,1),0),-ROUND(SUMPRODUCT(--(INDIRECT("Reserve!$"&SUBSTITUTE(ADDRESS(1,MATCH(C2,Reserve!$3:$3,0),4),1,"")&"$29:$"&SUBSTITUTE(ADDRESS(1,MATCH(C2,Reserve!$3:$3,0),4),1,"")&"$1458")),--(Reserve!$A$29:$A$1458=B2),--(Reserve!$W$29:$W$1458=A2))*LOOKUP(RIGHT(C2,1),{"2","3","4","5"},{0.25,0.5,0.75,1}),0))

This is what I use to book our reserve for inventory because the report that I receive from the field is not in a user-freindly fashion.  Which reminds me that I need to speak with them to get this in a better format.


----------



## pgc01 (Mar 5, 2008)

Hi all

This is my craziest formula and one of the craziest I've seen. The equivalent vba is just a couple of statements!

The formula extracts digits out of a string. Only works in xl2007 as some functions are deeply nested. Uses an auxilliary formula or else it would be unmanageable.

Extracts up to 30 digits.

I'm sure there must be a simpler solution (like, for ex., any other solution) but it was fun to build.

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center"  >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center"  width=30  >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center"  >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center"  width=30  >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center"  width=30  >E</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td  style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610</td><td  style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td  style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">01123581321345589144233377610</td><td  style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td  style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">35</td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td  style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">0 Mary 123-456 had a 7890 987.654 little 321 lamb 0</td><td  style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td  style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">012345678909876543210</td><td  style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td  style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">31</td></tr><tr><td colspan=6 style="background:#9CF; padding-left:1em" > [ExtractDigits.xlsb]Sol xl2007</td></tr></table><br>
<br>
<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; "  >Addr</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; "  >Formula</td></tr><tr><td colspan=2  style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; "  >[ExtractDigits.xlsb]Sol xl2007</td></tr><tr><td  rowspan=2  style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center; " >C1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em; " > =TEXT<span style="font-weight: 600;color: blue;">(</span>SUM<span style="font-weight: 600;color: maroon;">(</span>IF<span style="font-weight: 600;color: black;">(</span>ISNUMBER<span style="font-weight: 600;color: red;">(</span>-MID<span style="font-weight: 600;color: green;">(</span>A1,ROW<span style="font-weight: 600;color: fuchsia;">(</span>INDIRECT<span style="font-weight: 600;color: teal;">(</span>"1:"&E1<span style="font-weight: 600;color: teal;">)</span><span style="font-weight: 600;color: fuchsia;">)</span>,1<span style="font-weight: 600;color: green;">)</span><span style="font-weight: 600;color: red;">)</span>,MID<span style="font-weight: 600;color: red;">(</span>A1,ROW<span style="font-weight: 600;color: green;">(</span>INDIRECT<span style="font-weight: 600;color: fuchsia;">(</span>"1:"&E1<span style="font-weight: 600;color: fuchsia;">)</span><span style="font-weight: 600;color: green;">)</span>,1<span style="font-weight: 600;color: red;">)</span>*10^<span style="font-weight: 600;color: red;">(</span>10*E1-SUMPRODUCT<span style="font-weight: 600;color: green;">(</span>LEN<span style="font-weight: 600;color: fuchsia;">(</span>SUBSTITUTE<span style="font-weight: 600;color: teal;">(</span>LEFT<span style="font-weight: 600;color: olive;">(</span>A1,E1<span style="font-weight: 600;color: olive;">)</span>,ROW<span style="font-weight: 600;color: olive;">(</span>INDIRECT<span style="font-weight: 600;color: blue;">(</span>"1:10"<span style="font-weight: 600;color: blue;">)</span><span style="font-weight: 600;color: olive;">)</span>-1,""<span style="font-weight: 600;color: teal;">)</span><span style="font-weight: 600;color: fuchsia;">)</span><span style="font-weight: 600;color: green;">)</span>-10*ROW<span style="font-weight: 600;color: green;">(</span>INDIRECT<span style="font-weight: 600;color: fuchsia;">(</span>"1:"&E1<span style="font-weight: 600;color: fuchsia;">)</span><span style="font-weight: 600;color: green;">)</span>+MMULT<span style="font-weight: 600;color: green;">(</span>LEN<span style="font-weight: 600;color: fuchsia;">(</span>SUBSTITUTE<span style="font-weight: 600;color: teal;">(</span>LEFT<span style="font-weight: 600;color: olive;">(</span>A1,ROW<span style="font-weight: 600;color: blue;">(</span>INDIRECT<span style="font-weight: 600;color: maroon;">(</span>"1:"&E1<span style="font-weight: 600;color: maroon;">)</span><span style="font-weight: 600;color: blue;">)</span><span style="font-weight: 600;color: olive;">)</span>,TRANSPOSE<span style="font-weight: 600;color: olive;">(</span>ROW<span style="font-weight: 600;color: blue;">(</span>INDIRECT<span style="font-weight: 600;color: maroon;">(</span>"1:10"<span style="font-weight: 600;color: maroon;">)</span><span style="font-weight: 600;color: blue;">)</span>-1<span style="font-weight: 600;color: olive;">)</span>,""<span style="font-weight: 600;color: teal;">)</span><span style="font-weight: 600;color: fuchsia;">)</span>,--<span style="font-weight: 600;color: fuchsia;">(</span>ROW<span style="font-weight: 600;color: teal;">(</span>INDIRECT<span style="font-weight: 600;color: olive;">(</span>"1:10"<span style="font-weight: 600;color: olive;">)</span><span style="font-weight: 600;color: teal;">)</span>>0<span style="font-weight: 600;color: fuchsia;">)</span><span style="font-weight: 600;color: green;">)</span><span style="font-weight: 600;color: red;">)</span><span style="font-weight: 600;color: black;">)</span><span style="font-weight: 600;color: maroon;">)</span>,REPT<span style="font-weight: 600;color: maroon;">(</span>"0",10*E1-SUMPRODUCT<span style="font-weight: 600;color: black;">(</span>LEN<span style="font-weight: 600;color: red;">(</span>SUBSTITUTE<span style="font-weight: 600;color: green;">(</span>LEFT<span style="font-weight: 600;color: fuchsia;">(</span>A1,E1<span style="font-weight: 600;color: fuchsia;">)</span>,ROW<span style="font-weight: 600;color: fuchsia;">(</span>INDIRECT<span style="font-weight: 600;color: teal;">(</span>"1:10"<span style="font-weight: 600;color: teal;">)</span><span style="font-weight: 600;color: fuchsia;">)</span>-1,""<span style="font-weight: 600;color: green;">)</span><span style="font-weight: 600;color: red;">)</span><span style="font-weight: 600;color: black;">)</span><span style="font-weight: 600;color: maroon;">)</span><span style="font-weight: 600;color: blue;">)</span>&<span style="font-weight: 600;color: blue;">(</span>IF<span style="font-weight: 600;color: maroon;">(</span>LEN<span style="font-weight: 600;color: black;">(</span>A1<span style="font-weight: 600;color: black;">)</span>=E1,"",TEXT<span style="font-weight: 600;color: black;">(</span>SUM<span style="font-weight: 600;color: red;">(</span>IF<span style="font-weight: 600;color: green;">(</span>ISNUMBER<span style="font-weight: 600;color: fuchsia;">(</span>-MID<span style="font-weight: 600;color: teal;">(</span>RIGHT<span style="font-weight: 600;color: olive;">(</span>A1,LEN<span style="font-weight: 600;color: blue;">(</span>A1<span style="font-weight: 600;color: blue;">)</span>-E1<span style="font-weight: 600;color: olive;">)</span>,ROW<span style="font-weight: 600;color: olive;">(</span>INDIRECT<span style="font-weight: 600;color: blue;">(</span>"1:"&LEN<span style="font-weight: 600;color: maroon;">(</span>A1<span style="font-weight: 600;color: maroon;">)</span>-E1<span style="font-weight: 600;color: blue;">)</span><span style="font-weight: 600;color: olive;">)</span>,1<span style="font-weight: 600;color: teal;">)</span><span style="font-weight: 600;color: fuchsia;">)</span>,MID<span style="font-weight: 600;color: fuchsia;">(</span>RIGHT<span style="font-weight: 600;color: teal;">(</span>A1,LEN<span style="font-weight: 600;color: olive;">(</span>A1<span style="font-weight: 600;color: olive;">)</span>-E1<span style="font-weight: 600;color: teal;">)</span>,ROW<span style="font-weight: 600;color: teal;">(</span>INDIRECT<span style="font-weight: 600;color: olive;">(</span>"1:"&LEN<span style="font-weight: 600;color: blue;">(</span>A1<span style="font-weight: 600;color: blue;">)</span>-E1<span style="font-weight: 600;color: olive;">)</span><span style="font-weight: 600;color: teal;">)</span>,1<span style="font-weight: 600;color: fuchsia;">)</span>*10^<span style="font-weight: 600;color: fuchsia;">(</span>10*<span style="font-weight: 600;color: teal;">(</span>LEN<span style="font-weight: 600;color: olive;">(</span>A1<span style="font-weight: 600;color: olive;">)</span>-E1<span style="font-weight: 600;color: teal;">)</span>-SUMPRODUCT<span style="font-weight: 600;color: teal;">(</span>LEN<span style="font-weight: 600;color: olive;">(</span>SUBSTITUTE<span style="font-weight: 600;color: blue;">(</span>RIGHT<span style="font-weight: 600;color: maroon;">(</span>A1,LEN<span style="font-weight: 600;color: black;">(</span>A1<span style="font-weight: 600;color: black;">)</span>-E1<span style="font-weight: 600;color: maroon;">)</span>,ROW<span style="font-weight: 600;color: maroon;">(</span>INDIRECT<span style="font-weight: 600;color: black;">(</span>"1:10"<span style="font-weight: 600;color: black;">)</span><span style="font-weight: 600;color: maroon;">)</span>-1,""<span style="font-weight: 600;color: blue;">)</span><span style="font-weight: 600;color: olive;">)</span><span style="font-weight: 600;color: teal;">)</span>-10*ROW<span style="font-weight: 600;color: teal;">(</span>INDIRECT<span style="font-weight: 600;color: olive;">(</span>"1:"&LEN<span style="font-weight: 600;color: blue;">(</span>A1<span style="font-weight: 600;color: blue;">)</span>-E1<span style="font-weight: 600;color: olive;">)</span><span style="font-weight: 600;color: teal;">)</span>+MMULT<span style="font-weight: 600;color: teal;">(</span>LEN<span style="font-weight: 600;color: olive;">(</span>SUBSTITUTE<span style="font-weight: 600;color: blue;">(</span>LEFT<span style="font-weight: 600;color: maroon;">(</span>RIGHT<span style="font-weight: 600;color: black;">(</span>A1,LEN<span style="font-weight: 600;color: red;">(</span>A1<span style="font-weight: 600;color: red;">)</span>-E1<span style="font-weight: 600;color: black;">)</span>,ROW<span style="font-weight: 600;color: black;">(</span>INDIRECT<span style="font-weight: 600;color: red;">(</span>"1:"&LEN<span style="font-weight: 600;color: green;">(</span>A1<span style="font-weight: 600;color: green;">)</span>-E1<span style="font-weight: 600;color: red;">)</span><span style="font-weight: 600;color: black;">)</span><span style="font-weight: 600;color: maroon;">)</span>,TRANSPOSE<span style="font-weight: 600;color: maroon;">(</span>ROW<span style="font-weight: 600;color: black;">(</span>INDIRECT<span style="font-weight: 600;color: red;">(</span>"1:10"<span style="font-weight: 600;color: red;">)</span><span style="font-weight: 600;color: black;">)</span>-1<span style="font-weight: 600;color: maroon;">)</span>,""<span style="font-weight: 600;color: blue;">)</span><span style="font-weight: 600;color: olive;">)</span>,--<span style="font-weight: 600;color: olive;">(</span>ROW<span style="font-weight: 600;color: blue;">(</span>INDIRECT<span style="font-weight: 600;color: maroon;">(</span>"1:10"<span style="font-weight: 600;color: maroon;">)</span><span style="font-weight: 600;color: blue;">)</span>>0<span style="font-weight: 600;color: olive;">)</span><span style="font-weight: 600;color: teal;">)</span><span style="font-weight: 600;color: fuchsia;">)</span><span style="font-weight: 600;color: green;">)</span><span style="font-weight: 600;color: red;">)</span>,REPT<span style="font-weight: 600;color: red;">(</span>"0",10*<span style="font-weight: 600;color: green;">(</span>LEN<span style="font-weight: 600;color: fuchsia;">(</span>A1<span style="font-weight: 600;color: fuchsia;">)</span>-E1<span style="font-weight: 600;color: green;">)</span>-SUMPRODUCT<span style="font-weight: 600;color: green;">(</span>LEN<span style="font-weight: 600;color: fuchsia;">(</span>SUBSTITUTE<span style="font-weight: 600;color: teal;">(</span>RIGHT<span style="font-weight: 600;color: olive;">(</span>A1,LEN<span style="font-weight: 600;color: blue;">(</span>A1<span style="font-weight: 600;color: blue;">)</span>-E1<span style="font-weight: 600;color: olive;">)</span>,ROW<span style="font-weight: 600;color: olive;">(</span>INDIRECT<span style="font-weight: 600;color: blue;">(</span>"1:10"<span style="font-weight: 600;color: blue;">)</span><span style="font-weight: 600;color: olive;">)</span>-1,""<span style="font-weight: 600;color: teal;">)</span><span style="font-weight: 600;color: fuchsia;">)</span><span style="font-weight: 600;color: green;">)</span><span style="font-weight: 600;color: red;">)</span><span style="font-weight: 600;color: black;">)</span><span style="font-weight: 600;color: maroon;">)</span><span style="font-weight: 600;color: blue;">)</span> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;; " > This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.</td></tr><tr><td  rowspan=2  style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center; " >E1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em; " > =IF<span style="font-weight: 600;color: blue;">(</span>MAX<span style="font-weight: 600;color: maroon;">(</span>10*ROW<span style="font-weight: 600;color: black;">(</span>INDIRECT<span style="font-weight: 600;color: red;">(</span>"1:"&LEN<span style="font-weight: 600;color: green;">(</span>A1<span style="font-weight: 600;color: green;">)</span><span style="font-weight: 600;color: red;">)</span><span style="font-weight: 600;color: black;">)</span>-MMULT<span style="font-weight: 600;color: black;">(</span>LEN<span style="font-weight: 600;color: red;">(</span>SUBSTITUTE<span style="font-weight: 600;color: green;">(</span>LEFT<span style="font-weight: 600;color: fuchsia;">(</span>A1,ROW<span style="font-weight: 600;color: teal;">(</span>INDIRECT<span style="font-weight: 600;color: olive;">(</span>"1:"&LEN<span style="font-weight: 600;color: blue;">(</span>A1<span style="font-weight: 600;color: blue;">)</span><span style="font-weight: 600;color: olive;">)</span><span style="font-weight: 600;color: teal;">)</span><span style="font-weight: 600;color: fuchsia;">)</span>,TRANSPOSE<span style="font-weight: 600;color: fuchsia;">(</span>ROW<span style="font-weight: 600;color: teal;">(</span>INDIRECT<span style="font-weight: 600;color: olive;">(</span>"1:10"<span style="font-weight: 600;color: olive;">)</span><span style="font-weight: 600;color: teal;">)</span>-1<span style="font-weight: 600;color: fuchsia;">)</span>,""<span style="font-weight: 600;color: green;">)</span><span style="font-weight: 600;color: red;">)</span>,--<span style="font-weight: 600;color: red;">(</span>ROW<span style="font-weight: 600;color: green;">(</span>INDIRECT<span style="font-weight: 600;color: fuchsia;">(</span>"1:10"<span style="font-weight: 600;color: fuchsia;">)</span><span style="font-weight: 600;color: green;">)</span>>0<span style="font-weight: 600;color: red;">)</span><span style="font-weight: 600;color: black;">)</span><span style="font-weight: 600;color: maroon;">)</span><=15,LEN<span style="font-weight: 600;color: maroon;">(</span>A1<span style="font-weight: 600;color: maroon;">)</span>,MATCH<span style="font-weight: 600;color: maroon;">(</span>15,10*ROW<span style="font-weight: 600;color: black;">(</span>INDIRECT<span style="font-weight: 600;color: red;">(</span>"1:"&LEN<span style="font-weight: 600;color: green;">(</span>A1<span style="font-weight: 600;color: green;">)</span><span style="font-weight: 600;color: red;">)</span><span style="font-weight: 600;color: black;">)</span>-MMULT<span style="font-weight: 600;color: black;">(</span>LEN<span style="font-weight: 600;color: red;">(</span>SUBSTITUTE<span style="font-weight: 600;color: green;">(</span>LEFT<span style="font-weight: 600;color: fuchsia;">(</span>A1,ROW<span style="font-weight: 600;color: teal;">(</span>INDIRECT<span style="font-weight: 600;color: olive;">(</span>"1:"&LEN<span style="font-weight: 600;color: blue;">(</span>A1<span style="font-weight: 600;color: blue;">)</span><span style="font-weight: 600;color: olive;">)</span><span style="font-weight: 600;color: teal;">)</span><span style="font-weight: 600;color: fuchsia;">)</span>,TRANSPOSE<span style="font-weight: 600;color: fuchsia;">(</span>ROW<span style="font-weight: 600;color: teal;">(</span>INDIRECT<span style="font-weight: 600;color: olive;">(</span>"1:10"<span style="font-weight: 600;color: olive;">)</span><span style="font-weight: 600;color: teal;">)</span>-1<span style="font-weight: 600;color: fuchsia;">)</span>,""<span style="font-weight: 600;color: green;">)</span><span style="font-weight: 600;color: red;">)</span>,--<span style="font-weight: 600;color: red;">(</span>ROW<span style="font-weight: 600;color: green;">(</span>INDIRECT<span style="font-weight: 600;color: fuchsia;">(</span>"1:10"<span style="font-weight: 600;color: fuchsia;">)</span><span style="font-weight: 600;color: green;">)</span>>0<span style="font-weight: 600;color: red;">)</span><span style="font-weight: 600;color: black;">)</span>,0<span style="font-weight: 600;color: maroon;">)</span><span style="font-weight: 600;color: blue;">)</span> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;; " > This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.</td></tr></table>


----------



## Richard Schollar (Mar 6, 2008)

It seems there isn't a 'vomiting smilie' which is what I think I need after trying to decipher that formula, Pedro 

You crazy nutter!


----------



## pgc01 (Mar 6, 2008)

I'm very disappointed Richard. Why don't you like my beautiful formula? 

At least I hope you meant 
	

	
	
		
		

		
			





 and not


----------



## Domski (Mar 6, 2008)

pgc01 said:


> I'm very disappointed Richard. Why don't you like my beautiful formula?
> 
> At least I hope you meant
> 
> ...


 
It's like an Excel version of Joseph and His Amazing Technicolour Dreamcoat. Although thinking about it that was pretty vomit inducing too!


----------



## Oaktree (Mar 6, 2008)

No morefunc add-in, pgc?

C1 {=SUBSTITUTE(MCONCAT(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),""),"FALSE","")}


----------



## pgc01 (Mar 6, 2008)

Oaktree said:


> No morefunc add-in, pgc?
> 
> C1 {=SUBSTITUTE(MCONCAT(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),""),"FALSE","")}


 
Hi Oaktree

No. Just excel native functions, no vba, yours or someone else's (add-in).

The problem with this type of formulas is exactly that the worksheet function Concatenate() does not accept a range or an array.

This could be done in vba in a couple of statements, but then it would be no fun. 

I would not use this formula in real life but I thought it fits this thread, as it's about big and crazy formulas.


----------



## arkusM (Mar 11, 2008)

=IF(OR(BG$9=0,$Z135>BG$6,$AA135<BG$6),0,IF($AD135=1,($W135-BG$8)*($U135*BG$10)*BG$11,IF($AD135=2,($W135-BG$9)*$U135*BG$11,IF($AD135=3,ROUND(IF(AND(BG$9>$Y135,$Y135>0),($Y135-BG$9)*$U135*BG$11,IF(AND(BG$9<$X135,$X135>0),($X135-BG$9)*$U135*BG$11)),2),0))))
This one is used in an internal hedge calulation, it is looking to see if it should calc the hegde position.


=IF(ISERROR(INDEX('Production Information'!$F$3:$T$15000,SMALL(IF('Production Information'!$F$3:$F$15000=($C$5&$D$5&$E17),ROW('Production Information'!$F$3:$F$15000)-ROW('Production Information'!$F$3)+1,ROW('Production Information'!$F$15000)+1),V$6),V$7)),0,INDEX('Production Information'!$F$3:$T$15000,SMALL(IF('Production Information'!$F$3:$F$15000=($C$5&$D$5&$E17),ROW('Production Information'!$F$3:$F$15000)-ROW('Production Information'!$F$3)+1,ROW('Production Information'!$F$15000)+1),V$6),V$7)) 

This one was made bfore Aladin enlightened me with the 
	
	
	
	
	
	



```
=lookup(9.9999e+307,Choose({1,2},0,Formula))
```
 
This beast goes into a data dump and pulls out specific bits of information and I'm not sure I can remember how it even works anymore, but it works. Takes about 45 seconds to run....

One more...

=ROUND(IF(AND($BK97>=I$4,$BL97>=I$5,$BK97<I$5),I$5-$BK97+1,IF(AND($BL97>I$4,$BK97<=I$4,I$5>=$BL97),$BL97-I$4+1,IF(AND(($BK97+1)>I$4,$BL97>I$4,$BL97<I$5),$BL97-$BK97+1,IF(OR(AND($BK97<I$4,$BL97>I$5),AND($BK97=I$4,$BL97=I$5)),I$5-I$4+1,0))))/I$3*$BI97,1)

This one was difficult to get to work. Mostly because it had to verify if a date was before the first of the month, or started in the month, was after the end of the month, and then calculate appropriately. Because of the stuff I have learned on the board I probably could do this more efficiently.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>


----------



## deadlyduck (Apr 29, 2008)

```
=IF('Main Data Entry Sheet'!K157=1,SUM('Main Data Entry Sheet'!K156*'Main Data Entry Sheet'!K157,IF(AND('Main Data Entry Sheet'!K157=1,MID('Case I comp'!B263,1,1)="R"),'Case I comp'!B260*-1,0),'Main Data Entry Sheet'!K158*'Main Data Entry Sheet'!K159,IF(AND('Main Data Entry Sheet'!K159=1,MID('Case I comp'!C263,1,1)="R"),'Case I comp'!C260*-1,0)),SUM('Main Data Entry Sheet'!K156*'Main Data Entry Sheet'!K157,'Main Data Entry Sheet'!K158*'Main Data Entry Sheet'!K159))*'Main Data Entry Sheet'!B155
```
This formula computed the tax allowable value of Plant & machinery purchased during an accounting period  under current Irish Corporation tax legislation.  I had to allow for  factors such as :

1. date of purchase/ use;
2. whether a maximum allowed cost was exceeded;
3. whether the company had a 'replacement option' available to it (to defer a tax charge arising on the sale of an old asset).

The data in the Excel file was used to output (via mailmerge) both an exam question and solution in minutes rather than hours! It's very clunky (I was unaware at the time of the SUMPRODUCT function). As a medium term project I hope to improve the file but am feeling a little overwhelmed at the prospect!


----------



## Long Nose (Oct 3, 2008)

That Variant is way cool.  I'd not seen it either.  I'm in the, "I'm not worthy" category amongst the men of greatness here, but the one I'm still amazing over lately is this answer from Aladin.



> You could create on every relevant sheet an additional column, say, G, with"
> 
> =MONTH(A2)
> 
> ...


----------



## Patience (Oct 7, 2008)

One I was recently proud of:


=IF(AND(MATCH(Data!K2152,Props!$F$1:$F$211,0)<>11,
MATCH(Data!K2152,Props!$F$1:$F$211,0)<>12,MATCH(Data!K2152,Props!$F$1:$F$211,0)
<>14),CONCATENATE(K2152,RIGHT(Props!$K$26,8)),IF
(MATCH(Data!K2152,Props!$F$1:$F$211,0)=11,VLOOKUP(IF(ISNA(RIGHT(G2152,2)),
VALUE(RIGHT(G2152,2)),RIGHT(G2152,2)),Props!$J$2:$K$16,2,0),IF(
MATCH(Data!K2152,Props!$F$1:$F$211,0)=12,VLOOKUP(IF(ISNA(RIGHT(G2152,2)),
VALUE(RIGHT(G2152,2)),RIGHT(G2152,2)),Props!$J$29:$K$39,2,0),VLOOKUP(IF(ISNA(
RIGHT(G2152,2)),VALUE(RIGHT(G2152,2)),RIGHT(G2152,2)),Props!$J$19:$K$23,2,0))))


Quite straightforward really.


----------



## arkusM (Oct 7, 2008)

I was quite happy that I got the below to work, faking-out Excel by bypassing the volitile function limits!


```
=LOOKUP(9.99999E+307,CHOOSE({1,2},SUM(*Top_RNG*:*T_End_Rng*),*Volume_Lookup*))
```


```
*Top_Rng*  =INDIRECT(ADDRESS(ROW()-1,COLUMN()))
*T_End_Rng* =INDIRECT(ADDRESS(ROW()-*T_Col*,COLUMN()))
*T_Col* =INDIRECT(ADDRESS(ROW(),7))
*Volume_Lookup* =VLOOKUP(Revenue!$F27,Rec_PTS_MN,Revenue!N$1,0)
```
 
For formatting

```
=MOD(ROW()-14,1*2)+1<=1
```
[/quote]

The original post that I posted on this problem:
http://www.mrexcel.com/forum/showthread.php?t=340445

And the post that I asked a more general question about this:
http://www.mrexcel.com/forum/showthread.php?p=1696781#post1696781


----------



## SydneyGeek (Oct 8, 2008)

A while back I had to create a formula that spread costs over an S-curve. This was the beast that did the job:


```
=(IF(OR($F27<BV$15,$G27>BW$15,BW$15>$I27),0,IF(AND($C27<=BV$15,$F27>=BW$15),IF($E27="F",6/$D27,SUM(INDEX(INDIRECT($K27),$L27,DATEDIF($C27,BV$15,"M")+3):INDEX(INDIRECT($K27),$L27,DATEDIF($C27,BW$15,"M")+2))),IF(AND($C27<=BV$15,$F27<BW$15),IF($E27="F",$J27/$D27,SUM(INDEX(INDIRECT($K27),$L27,$D27-$J27+2):INDEX(INDIRECT($K27),$L27,$D27+1))),IF(AND($C27>BV$15,$F27>=BW$15),IF($E27="F",$H27/$D27,SUM(INDEX(INDIRECT($K27),$L27,2):INDEX(INDIRECT($K27),$L27,$H27+1))),1))))*$D151*$L151)*BW$9
```

The auditor who had to check it out, ended up taking my word for it.

Denis


----------



## repairman615 (May 3, 2011)

=OR("To take a decimal number that represents inches and convert to a fractional." , "To revive an old thread.") 

AE20 has the number.

=IFERROR(IF(LOOKUP(VALUE("0"&RIGHT(AE20,LEN(AE20)-FIND(".",AE20,1)+1)),{0,0.001,0.0625,0.125,0.1875,0.25,0.3125,0.375,0.4375,0.5,0.5625,0.625,0.6875,0.75,0.8125,0.875,0.9375},{" - 0"," - 1/16"," - 1/8"," - 3/16"," - 1/4"," - 5/16"," - 3/8"," - 7/16"," - 1/2"," - 9/16"," - 5/8"," - 11/16"," - 3/4"," - 13/16"," - 7/8"," - 15/16","trigger"})="trigger",LEFT(AE20,FIND(".",AE20,1)-1)+1&" - 0"&"""",LEFT(AE20,FIND(".",AE20,1)-1)&LOOKUP(VALUE("0"&RIGHT(AE20,LEN(AE20)-FIND(".",AE20,1)+1)),{0,0.001,0.0625,0.125,0.1875,0.25,0.3125,0.375,0.4375,0.5,0.5625,0.625,0.6875,0.75,0.8125,0.875,0.9375},{" - 0"," - 1/16"," - 1/8"," - 3/16"," - 1/4"," - 5/16"," - 3/8"," - 7/16"," - 1/2"," - 9/16"," - 5/8"," - 11/16"," - 3/4"," - 13/16"," - 7/8"," - 15/16","trigger"})&""""),AE20&"""")


----------

