Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Fun formulas

This is a discussion on Fun formulas within the Lounge v.2.0 forums, part of the The Lounge category; Hi All. It's quiet here and I was wondering if anyone had any fun or interesting formulas that are not ...

  1. #1
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,884

    Default Fun formulas

    Hi All.

    It's quiet here and I was wondering if anyone had any fun or interesting formulas that are not based on any data within the spreadsheet...

    =SUBSTITUTE(ADDRESS(1,COLUMN(H1),4),"1","")&SUBSTITUTE(ADDRESS(1,COLUMN(E1),4),"1","")&SUBSTITUTE(ADDRESS(1,COLUMN(L1),4),"1","")&SUBSTITUTE(ADDRESS(1,COLUMN(L1),4),"1","")&SUBSTITUTE(ADDRESS(1,COLUMN(O1),4),"1","")

    Simple, but this is the type of formula I was thinking about.

    Ak
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  2. #2
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,632

    Default Re: Fun formulas

    Hi,

    Try:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DEC2HEX(2298433)&DEC2HEX(-404887622166)&DEC2HEX(324135473)&DEC2HEX(59774192177)&DEC2HEX(150653386305)&DEC2HEX(44052435364)&DEC2HEX(7429275375),1," "),2,"I"),3,"T"),4,"S"),5,"O")
    Last edited by fairwinds; Dec 3rd, 2011 at 06:01 PM.

  3. #3
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    7,084

    Default Re: Fun formulas

    That's very good advice fairwinds . . .
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  4. #4
    Board Regular TinaP's Avatar
    Join Date
    Jan 2005
    Location
    Ohio
    Posts
    488

    Default Re: Fun formulas

    It's kind of forced, but try this:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DEC2HEX(584167),"7","R1"),"8","N"),"9","V")&DEC2HEX(234)&CHAR(84)&" "&CHAR(80)&SUBSTITUTE("GUN","G","R")&DEC2HEX(14)&"S1"&SUBSTITUTE("WHERE","RE","N ")&CHAR(89)&CHAR(79)&CHAR(85)&CHAR(49)&SUBSTITUTE(DEC2HEX(2766),"C","R")&1&REPLACE(DEC2HEX(64237),3,0,"MISH"),1," ")

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    56

    Default Re: Fun formulas

    Fascinating. Any chance someone could explain one of those formulas?

  6. #6
    MrExcel MVP
    Moderator

    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    9,837

    Default Re: Fun formulas

    Quote Originally Posted by mccdaddy View Post
    Fascinating. Any chance someone could explain one of those formulas?
    There are plenty here that would do a better job explaining it but as noone has replied (yet) I'll have a crack.

    Fairwinds is (very cleverly) exploiting hexadecimal notation to create words, since hexadecimal numbers are represented as strings containing # characters [0-9] and letters [a-f].

    From wikipedia:
    In mathematics and computer science, hexadecimal (also base 16, or hex) is a positional numeral system with a radix, or base, of 16. It uses sixteen distinct symbols, most often the symbols 0–9 to represent values zero to nine, and A, B, C, D, E, F (or alternatively a–f) to represent values ten to fifteen. For example, the hexadecimal number 2AF3 is equal, in decimal, to (2 163) + (10 162) + (15 161) + (3 160), or 10,995
    Take the following example:
    Excel 2010
    ABCDE
    1Textnevereatyellowsnow
    2Hex Value (after substitutes)1e2e3ea45e66789178
    3Dec Value1236193748618661637240
    4
    5ReplaceWith
    6n1
    7v2
    8r3
    9t4
    10y5
    11l6
    12o7
    13w8
    14s9

    Sheet1



    Worksheet Formulas
    CellFormula
    B3=HEX2DEC(B2)



    Have a look at the words in B1:E1. At the moment these are just text values but we will want to represent these as valid hex values. Since only letters [a-f] apply we need to substitute any letters greater than 'f' with a numeric value. I've chosen to number the letters 1-9 in the order of appearance, the substitution visible in table B6:C14.

    The substituted values are visible in B2:E2. The value that lie beneath in row 3 are the decimal values, here using the HEX2DEC worksheet function to tell us which values apply.

    Now in order to wrap this up into a single formula giving us the entire text string we need to:

    1. Convert each dec value to its' hex value, and concatenate the result to the previous result

    2. Perform the substitutions per what is seen in the substitution table above.

    Hence we get:
    Code:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DEC2HEX(123619)&DEC2HEX(3748)&DEC2HEX(6186616)&DEC2HEX(37240),1,"N"),2,"V"),3,"R "),4,"T "),5,"Y"),6,"L"),7,"O"),8,"W "),9,"S")
    Stick this in a cell, and then use the Evaluate Formula tool (on the Formulas tab) to step through and check what happens at each step.
    Regards,
    Jon von der Heyden

    - Posting guidelines, forum rules and terms of use

    - Post your sheet using HTML tables - why?

    - Read the FAQs

    - Always use CODE tags when posting code, e.g. [CODE]insert code here[/CODE]

    - My Excel Blog

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Posts
    56

    Default Re: Fun formulas

    Thanks for taking the time to explain this. I haven't grasped it fully yet but I'm working on it.

  8. #8
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    13,061

    Default Re: Fun formulas

    How about VBA?

    Code:
    Sub shg()
        Dim i As Long
        Dim s As String
        
        For i = 0 To 3
            s = s & Chr((-40 * i ^ 3 + 135 * i ^ 2 - 128 * i + 345) / 3)
        Next i
        
        For i = 0 To 3
            s = s & Chr((-146 * i ^ 3 + 543 * i ^ 2 - 481 * i + 690) / 6)
        Next i
        
        For i = 0 To 4
            s = s & Chr((95 * i ^ 4 - 682 * i ^ 3 + 1393 * i ^ 2 - 638 * i + 2496) / 24)
        Next i
        MsgBox s
    End Sub

  9. #9
    MrExcel MVP
    Moderator

    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    9,837

    Default Re: Fun formulas

    Howdy! Very tidy shg
    Regards,
    Jon von der Heyden

    - Posting guidelines, forum rules and terms of use

    - Post your sheet using HTML tables - why?

    - Read the FAQs

    - Always use CODE tags when posting code, e.g. [CODE]insert code here[/CODE]

    - My Excel Blog

  10. #10
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Fun formulas

    http://j-walkblog.com/index.php?/web...Quine_Formula/

    The result of the formula is the formula itself.
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com