IT:99 Bottles of Beer (Microsoft Excel)
From Stiki
The formulae listed below can be used to produce the lyrics to traditional repetitive songs. The American version produces the lyrics to 99 Bottles of Beer and the British version produces the lyrics to 99 Green Bottles Hanging on the Wall.
In order to use these formulae, copy them into a cell in the spreadsheet as required, and then copy that cell down as far as you like. The formulae will write the complete song lyrics given enough rows, followed by a blank line, and then will begin writing the lyrics again from the beginning. Note that whichever cell you have pasted the formula into, the cell above it must be blank.
Note that you should copy the version that is 'Formatted for copying the formula' as this does not include any line breaks. The version that is 'Formatted for viewing the structure of the formula' includes additional line breaks and spacing that will prevent the formula from working in Excel, but aid understanding of how the formula works.
Contents |
American version
Standard reference style
This formula should be entered into cell A4 and then copied down. One iteration of the lyrics will occupy 397 rows.
| Formatted for copying the formula |
|---|
=IF(A3="","99 bottles of beer on the wall",IF(LEFT(A3,1)="N","",IF(AND(OR(A2="1 bottle of beer",A2="2 bottles of beer"),A2<>""),IF(LEFT(A2)="1","No more bottles of beer on the wall","1 bottle of beer on the wall"),IF(RIGHT(A3,1)="d",FIXED(VALUE(LEFT(A2,FIND(" ",A2,1)-1))-1,0,TRUE)&RIGHT(A1,LEN(A1)-FIND(" ",A1,1)+1),IF(RIGHT(A3,1)="l",IF(AND(A3<>A2,A2<>""),A3,LEFT(A3,LEN(A3)-12)),IF(RIGHT(A3,1)="r","Take one down and pass it around",NA()))))))
|
| Formatted for viewing the structure of the formula |
|---|
=IF(
A3="",
"99 bottles of beer on the wall",
IF(
LEFT(A3,1)="N",
"",
IF(
AND(
OR(
A2="1 bottle of beer",
A2="2 bottles of beer"
),
A2<>""
),
IF(
LEFT(A2)="1",
"No more bottles of beer on the wall",
"1 bottle of beer on the wall"
),
IF(
RIGHT(A3,1)="d",
FIXED(
VALUE(
LEFT(
A2,
FIND(" ",A2,1)-1
)
)-1,
0,
TRUE
)&RIGHT(
A1,
LEN(A1)-FIND(" ",A1,1)+1
),
IF(
RIGHT(A3,1)="l",
IF(
AND(
A3<>A2,
A2<>""
),
A3,
LEFT(A3,LEN(A3)-12)
),
IF(
RIGHT(A3,1)="r",
"Take one down and pass it around",
NA()
)
)
)
)
)
)
|
R1C1 reference style
This formula can be entered into any cell (in at least the fourth row) and then copied down. One iteration of the lyrics will occupy 397 rows.
| Formatted for copying the formula |
|---|
=IF(R[-1]C="","99 bottles of beer on the wall",IF(LEFT(R[-1]C,1)="N","",IF(AND(OR(R[-2]C="1 bottle of beer",R[-2]C="2 bottles of beer"),R[-2]C<>""),IF(LEFT(R[-2]C)="1","No more bottles of beer on the wall","1 bottle of beer on the wall"),IF(RIGHT(R[-1]C,1)="d",FIXED(VALUE(LEFT(R[-2]C,FIND(" ",R[-2]C,1)-1))-1,0,TRUE)&RIGHT(R[-3]C,LEN(R[-3]C)-FIND(" ",R[-3]C,1)+1),IF(RIGHT(R[-1]C,1)="l",IF(AND(R[-1]C<>R[-2]C,R[-2]C<>""),R[-1]C,LEFT(R[-1]C,LEN(R[-1]C)-12)),IF(RIGHT(R[-1]C,1)="r","Take one down and pass it around",NA()))))))
|
| Formatted for viewing the structure of the formula |
|---|
=IF(
R[-1]C="",
"99 bottles of beer on the wall",
IF(
LEFT(R[-1]C,1)="N",
"",
IF(
AND(
OR(
R[-2]C="1 bottle of beer",
R[-2]C="2 bottles of beer"
),
R[-2]C<>""
),
IF(
LEFT(R[-2]C)="1",
"No more bottles of beer on the wall",
"1 bottle of beer on the wall"
),
IF(
RIGHT(R[-1]C,1)="d",
FIXED(
VALUE(
LEFT(
R[-2]C,
FIND(" ",R[-2]C,1)-1
)
)-1,
0,
TRUE
)&RIGHT(
R[-3]C,
LEN(R[-3]C)-FIND(" ",R[-3]C,1)+1
),
IF(
RIGHT(R[-1]C,1)="l",
IF(
AND(
R[-1]C<>R[-2]C,
R[-2]C<>""
),
R[-1]C,
LEFT(R[-1]C,LEN(R[-1]C)-12)
),
IF(
RIGHT(R[-1]C,1)="r",
"Take one down and pass it around",
NA()
)
)
)
)
)
)
|
British version
Standard reference style
This formula should be entered into cell A3 and then copied down. One iteration of the lyrics will occupy 397 rows.
| Formatted for copying the formula |
|---|
=IF(A2="","99 green bottles hanging on the wall",IF(ISERR(FIND("no",A2)),IF(LEFT(A2,1)="A","There'll be "&IF(LEFT(A1,2)="1 ","no"&SUBSTITUTE(RIGHT(A1,33)," h","s h"),IF(LEFT(A1,2)="2 ","1"&SUBSTITUTE(RIGHT(A1,34),"es","e"),VALUE(LEFT(A1,2)-1)&RIGHT(A1,LEN(A1)-FIND(" ",A1)+1))),IF(OR(A1="",LEFT(A1,1)="T"),A2,IF(ISERR(VALUE(LEFT(A1,2))+VALUE(LEFT(A2,2))),IF(LEFT(A2,1)="T",SUBSTITUTE(A2,"There'll be ",""),NA()),"And if 1 green bottle should accidentally fall"))),""))
|
| Formatted for viewing the structure of the formula |
|---|
=IF(
A2="",
"99 green bottles hanging on the wall",
IF(
ISERR(FIND("no",A2)),
IF(
LEFT(A2,1)="A",
"There'll be "&IF(
LEFT(A1,2)="1 ",
"no"&SUBSTITUTE(RIGHT(A1,33)," h","s h"),
IF(
LEFT(A1,2)="2 ",
"1"&SUBSTITUTE(RIGHT(A1,34),"es","e"),
VALUE(
LEFT(A1,2)-1
)&RIGHT(
A1,
LEN(A1)-FIND(" ",A1)+1
)
)
),
IF(
OR(
A1="",
LEFT(A1,1)="T"
),
A2,
IF(
ISERR(VALUE(LEFT(A1,2))+VALUE(LEFT(A2,2))),
IF(
LEFT(A2,1)="T",
SUBSTITUTE(A2,"There'll be ",""),
NA()
),
"And if 1 green bottle should accidentally fall"
)
)
),
""
)
)
|
R1C1 reference style
This formula can be entered into any cell (in at least the third row) and then copied down. One iteration of the lyrics will occupy 397 rows.
| Formatted for copying the formula |
|---|
=IF(R[-1]C="","99 green bottles hanging on the wall",IF(ISERR(FIND("no",R[-1]C)),IF(LEFT(R[-1]C,1)="A","There'll be "&IF(LEFT(R[-2]C,2)="1 ","no"&SUBSTITUTE(RIGHT(R[-2]C,33)," h","s h"),IF(LEFT(R[-2]C,2)="2 ","1"&SUBSTITUTE(RIGHT(R[-2]C,34),"es","e"),VALUE(LEFT(R[-2]C,2)-1)&RIGHT(R[-2]C,LEN(R[-2]C)-FIND(" ",R[-2]C)+1))),IF(OR(R[-2]C="",LEFT(R[-2]C,1)="T"),R[-1]C,IF(ISERR(VALUE(LEFT(R[-2]C,2))+VALUE(LEFT(R[-1]C,2))),IF(LEFT(R[-1]C,1)="T",SUBSTITUTE(R[-1]C,"There'll be ",""),NA()),"And if 1 green bottle should accidentally fall"))),""))
|
| Formatted for viewing the structure of the formula |
|---|
=IF(
R[-1]C="",
"99 green bottles hanging on the wall",
IF(
ISERR(FIND("no",R[-1]C)),
IF(
LEFT(R[-1]C,1)="A",
"There'll be "&IF(
LEFT(R[-2]C,2)="1 ",
"no"&SUBSTITUTE(RIGHT(R[-2]C,33)," h","s h"),
IF(
LEFT(R[-2]C,2)="2 ",
"1"&SUBSTITUTE(RIGHT(R[-2]C,34),"es","e"),
VALUE(
LEFT(R[-2]C,2)-1
)&RIGHT(
R[-2]C,
LEN(R[-2]C)-FIND(" ",R[-2]C)+1
)
)
),
IF(
OR(
R[-2]C="",
LEFT(R[-2]C,1)="T"
),
R[-1]C,
IF(
ISERR(VALUE(LEFT(R[-2]C,2))+VALUE(LEFT(R[-1]C,2))),
IF(
LEFT(R[-1]C,1)="T",
SUBSTITUTE(R[-1]C,"There'll be ",""),
NA()
),
"And if 1 green bottle should accidentally fall"
)
)
),
""
)
)
|
See also
- IT:99 Bottles of Beer (Lotus 1-2-3) for the equivalent cell formulae to be used in Lotus 1-2-3.
