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

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

Ak

2. ## 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")

3. ## Re: Fun formulas

That's very good advice fairwinds . . .

4. ## 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. ## Re: Fun formulas

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

6. ## Re: Fun formulas

Originally Posted by mccdaddy
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.

7. ## 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. ## Re: Fun formulas

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. ## Re: Fun formulas

Howdy! Very tidy shg

10. ## Re: Fun formulas

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

The result of the formula is the formula itself.

Page 1 of 2 12 Last

#### Posting Permissions

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