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

[arkusM]

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]

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]

=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&"""")